Hello

I have a list of peoples names which are prefixed by a
title. The prefixes cannot be separated into another
column.

Is there any way I can Find the whole of the persons name,
without including the prefix.

eg if myname = Susan Brown
can I find it if the cell contains Mrs Susan Brown


Any help much appreciated

Sub FindSusan()
Set rng = Cells.Find(What:="Susan Brown", _
LookIn:=xlValues, Lookat:=xlPart, MatchCase:=False)

If Not rng Is Nothing Then
rng.Activate
End If

End Sub
as a basic approach.

This will find Susan Browning, so you might want to put this in a loop that
finds all possible matches, then evaluate the possibilities for the best or
exact match.

--
Regards,
Tom Ogilvy





"FRAN" wrote in message
...
Hello

I have a list of peoples names which are prefixed by a
title. The prefixes cannot be separated into another
column.

Is there any way I can Find the whole of the persons name,
without including the prefix.

eg if myname = Susan Brown
can I find it if the cell contains Mrs Susan Brown


Any help much appreciated

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

-----Original Message-----
Hello

I have a list of peoples names which are prefixed by a
title. The prefixes cannot be separated into another
column.

Is there any way I can Find the whole of the persons
name,
without including the prefix.

eg if myname = Susan Brown
can I find it if the cell contains Mrs Susan Brown


Any help much appreciated
.