Search and replace

[pe]

I need to search 5000 rows for "Q1. " ... "Q71. " and delete the question and numeral part. Then i need to make the font underlined in the cell and "proper case", it's now all caps.

Something like...
for i=1 to 5000
search for Q.##
delete Q.##
activecell.select
Selection.Font.Underline = xlUnderlineStyleSingle
make propercase
next i

Does anyone have a function to make ALL CAPS into All Caps. Also, I don't know how to delete Q.1 to Q.71 and keep the rest of the question. For instance, Q.1 HOW DO YOU FEEL TODAY?

It needs to read How Do You Feel Today? (underlined).

any help is greatly appreciated. thank you

 

[monak]

hi pe,


I think you need implement a range. Try this link. It documentation specifically VBA programming for Excell. They talk about ranges in detail.

http://www.dummies.com/extras/vba_fd_3e/08563BonusC.pdf

Mona

 

[monak]

hi pe,


I think you need to implement a range. Try this link. It documentation specifically VBA programming for Excell. They talk about ranges in detail.

http://www.dummies.com/extras/vba_fd_3e/08563BonusC.pdf

Mona

 

[DaleWatson123321]

Here's a formula option, which could work well, depending on what other data you might have in addition to the questions.

=IF(LEFT(A1,2)<>&quot;Q.&quot;,&quot;&quot;,PROPER(MID(A1,FIND(&quot; &quot;,A1,1)+1,999)))

It would be a matter of copying the formula down in an adjacent column, converting to values, and then paste on top of the existing data.

Hope this helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[pe]

Hey, that's a good one. It works and thanks so much