Is there any easy way to search for a specific word within a cell?
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.