Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    search and replace (excel xp)

    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

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: search and replace (excel xp)

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

    <pre>
    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
    </pre>

    Legare Coleman

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: search and replace (excel xp)

    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

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: search and replace (excel xp)

    Try This:

    <pre>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
    </pre>

    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •