search and replace (excel xp)

Topic

New Reply

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

Reply | Quote

Replies

If the questions are in column A of “Sheet1”, the following works for me:

Public Sub FixQ()
Dim I As Long, lRows As Long
    lRows = Worksheets("Sheet1").Range("A65536").End(xlUp).Row - 1
    With Worksheets("Sheet1").Range("A1")
        For I = 0 To lRows
            .Offset(I, 0).Value = Trim(.Offset(I, 0).Value)
            If UCase(Left(.Offset(I, 0).Value, 1)) = "Q" Then
                .Offset(I, 0).Value = Right(.Offset(I, 0).Value, _
                  Len(.Offset(I, 0).Value) - InStr(.Offset(I, 0).Value, " "))
            End If
            .Offset(I, 0).Value = Application.WorksheetFunction.Proper(.Offset(I, 0).Value)
        Next I
    End With
End Sub

Reply | Quote

thank you so much.

It is making all the rows proper, so should i move that statement up into the IF loop? Also, i need to underline the cell that used to have the Q. in it. I tried activecell.activate, and then underlined but it only did it for the first one. Thank you for your help

Reply | Quote

Try This:

Public Sub FixQ()
Dim I As Long, lRows As Long
    lRows = Worksheets("Sheet1").Range("A65536").End(xlUp).Row - 1
    With Worksheets("Sheet1").Range("A1")
        For I = 0 To lRows
        .Offset(I, 0).Value = Trim(.Offset(I, 0).Value)
            If UCase(Left(.Offset(I, 0).Value, 1)) = "Q" Then
                .Offset(I, 0).Value = Right(.Offset(I, 0).Value, _
                Len(.Offset(I, 0).Value) - InStr(.Offset(I, 0).Value, " "))
                .Offset(I, 0).Value = Application.WorksheetFunction.Proper(.Offset(I, 0).Value)
                .Offset(I, 0).Font.Underline = xlUnderlineStyleSingle
            End If
        Next I
    End With
End Sub

Reply | Quote