microsoft excel 2007 - Is there any easy way to search for a specific word within a cell?

For reasons that aren't necessary to add here, I have an Excel 2007 spreadsheet that contains text in several cells. The text in these cells ranges from less than 100 words to almost 500 in some cases. Every once in a while, I need to find one word within some of these cells.

Previously, I had been just scanning the cell with my eye after using Excel's search feature to narrow down which cells contained the word. While doing this, I got to wondering if there might be a more efficient way to go about this. I also began wondering how much more tedious and prone to error this method might be if I were working with cells that contained even more text. Is there any easy way to conduct a search within a cell, in other words, without resorting to some complicated formula or the use of VBA?

Granted, I could simply copy and paste the text into a Word document and pinpoint the word through CTRL+F in Word, but is there an easy way to simply search for and identify a specific word within a cell in Excel? I've pasted a visual of this for you below:

In the cell you see above, I have highlighted the word "dispuestos" after scanning this cell with my eye. Is there a way to easily do the same using an Excel search feature? For example, in a Word table, I know that using the tab key can take me from cell to cell, but if I want to tab within a cell, I simply press CTRL+TAB. Is there some sort of similar technique I can use to search within an Excel cell? Is there something I need to alter in the options for Excel to make this happen?

Answer

I'm thinking the only way is VBA, so I've broken the rules to write a macro (...sorry) for you that can achieve this quickly. It will colour every occurrence of the search term orange and tell you how many occurrences it finds. You could create a shortcut to it like CTRL+SHIFT+F (like a special CTRL+F).

By the way, it works the same as CTRL+F as far as selections go. That is to say, if only one cell is selected then it searches the whole sheet... but if a range of cells is selected then it only checks that specific range, just like the regular Find function.

Here's an example result:

And here's the VBA:

Sub findPaintString()
    Dim values As Range
    Dim LastRow As Long, LastCol As Integer

    myName = "Find+Paint String"

    'We'll work like the normal Find/Replace function which looks at the selected range...
    Set values = Selection

    '...if the selected range is one cell then we look at the entire worksheet (within the used range):
    If values.Cells.Count = 1 Then
        LastRow = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
        LastCol = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
        Set values = Range(Cells(1, 1), Cells(LastRow, LastCol))
    End If

    'Set a suggested/default search query if you repeatedly search the same word:
    strSearch = ""

    'Get the string to paint:
    theString = CStr(InputBox("Enter the string you want to paint" & vbNewLine & "(not case sensitive):", myName, strSearch))
    If theString = "" Then Exit Sub

    'Set the colour to paint occurrences:
    theColour = 1137094

    'Make a log of occurrences:
    foundLog = 0

    'Work through each cell in range, searching for the string and painting it:
    For Each cell In values
        'Check if our string is somewhere in the cell - if not then ignore it:
        If InStr(LCase(cell.Value), LCase(theString)) Then
            matchLog = 0 'match success log (increments by 1 per character)
            j = 1 ' string character selector
            For i = 1 To cell.Characters.Count
                If LCase(Mid(cell.Value, i, 1)) = LCase(Mid(theString, j, 1)) Then
                    matchLog = matchLog + 1 '+1 to matchlog
                    j = j + 1 '+1 to string character selector
                    If matchLog = Len(theString) Then
                        'we have found the full word, so paint it:
                        cell.Characters(i - Len(theString) + 1, Len(theString)).Font.Color = theColour
                        j = 1 'reset string character ready for next use
                        matchLog = 0 'reset matchLog ready for next use
                        foundLog = foundLog + 1
                    End If
                Else
                    'reset matchLog and string character selector:
                    matchLog = 0
                    j = 1

                    'see if this cell character (which didn't match the string character that we
                    'got up to) matches the first string character:
                    If LCase(Mid(cell.Value, i, 1)) = LCase(Mid(theString, j, 1)) Then
                        matchLog = matchLog + 1
                        j = j + 1
                    End If
                End If
            Next i
        End If 'in string
    Next cell

    'Tidy data for message box:
    If Len(theString) > 20 Then theString = Left(theString, 16) & "..."
    If foundLog = 0 Then
        foundLog = "0"
        theS = "s"
    ElseIf foundLog = 1 Then
        theS = ""
    Else
        theS = "s"
    End If
    MsgBox "Found " & foundLog & " occurrence" & theS & " of '" & theString & "'.", vbOKOnly, myName
End Sub

It is case insensitive. To make it case sensitive, remove the four instances of LCase().

If you frequently search the same string then change the line strSearch = "" line to [for example] strSearch = "apples". You can still overwrite it when you launch it.