finding text in cells

  • Hi All,

    I have a spreadsheet where each cell in the range A1:A20 has a list of concatenated names, e.g. Jack, Mary, Jim, Stuart, Tracy etc. Is it possible to highlight all of the cells where the same two names appear e.g. Jack and Stuart or Jack and Jim. If not highlighting, is it possible to count how many times the two names, which can be typed in any free cell appear in the same cells in the range A1:A20?

    Thanks for any help in advance.


  • Hi Ranger,

    try this if a macro is OK with you

    Sub CountHighlightNames()

    Dim Name As String, Rw As Integer, Count As Integer

    Count = 0

    Name = InputBox("Name to search for")

    For Rw = 1 To 20
    Cells(Rw, 1).Interior.ColorIndex = xlNone
    If InStr(1, Cells(Rw, 1), Name, 1) Then
    Count = Count + 1
    Cells(Rw, 1).Interior.ColorIndex = 6
    End If

    MsgBox "Found " & Name & " " & Count & " times"

    End Sub

    The search is not case senesitive.

    ie. jack will still find Jack

    Cells should become yellow if the name is found!

  • One Way using a formula;
    Enter formula in B1, notice you will have to include the comma,


    Using the cell reference;
    Where A1 is the name you want to count

    You could use Conditional Formatting for your Highlighting options. Go To Format>Conditional Formatting and you can enter up to 3 conditions for what you want.

    If you want to trim the comma out of the names and place in a seperate column you could use this

    =TRIM(RIGHT(D2,LEN(D2)-FIND(",",D2))) & " " & TRIM(LEFT(D2,FIND(",",D2,1)-1))

    If a name was in Cell D2 like this Schmoe, Joe, you would put this formula in cell D3 and it would return the name Joe Schmoe.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!