Finding a string

[Dag Johansen]

Do you want to check if a particular cell contains a
substring, or do you want to search for cells that contain
the substring?

If you simply need to check if a cell contains a
substring, that is straightforward:

Function CellContainsText(cell As Range, s As String) As
Boolean
CellContainsText = (InStr(cell.Value, s) > 0)
End Function

Sub test()
If CellContainsText(ActiveCell, "Susan Brown") Then
MsgBox "Yep"
Else
MsgBox "Nope"
End If
End Sub

Put this code in a standard module, choose a cell and
press ALT+F8 to see the result.

But this might not be enough; A cell containing "Mrs Susan
BrownCastle" would match, as it does contain the
substring. Searching for " Susan Brown " (note the leading
and trailing spaces) solves that problem, but does not
match "Mrs.Susan Brown" (no spaces) or "Mrs. Susan Brown"
(no trailing space) or even simply "Susan Brown"!

You need something that can do regular expressions pattern
matching; it would simplify your life a great deal!
Instead of only specifying literal characters to match you
can use pre-defined or define your own sets of characters
to match in a pattern; in this case you want "word-
boundary" (\w) followed by "Susan" followed by \w followed
by "Brown" followed by \w, or \wSusan\wBrown\w; that would
match whether there's a space or a tab between words;
whether there's a new line or not and so on.

Hope this wasn't too discouraging, good luck! For what
it's worth, you'll learn some really useful programming
technique doing it this way.

Dag Johansen