I'm trying to create a mailing list and have a list containing Mr, Ms, Miss,
Mrs, Dr, etc. Some names have no salutations. I want to use a macro that
looks for the first name if and after one of these surnames occurs.
I tried multiple IF's, as below?
=IF(LEFT(A31,3)="Mrs","Mrs",IF(LEFT(A31,4)="Miss","Miss",IF(LEFT(A31,2)=OR("Mr","Ms","Dr"),LEFT(A31,2),"")))
The frst part works for Mrs * Miss, but the OR section gives me a #Value
error.
I there a better way?
Jim Berglund
I think your description of what you eventually want to do may not be complete.
But here is a UDF that will, depending on the argument, return either an
initial Title if it is present (or blank if it is not).
It will also return the first word after the title or, if there is no title,
return the first word.
I could imagine that sometimes the first word after the title might be a last
name, rather than a first name.
In any event, to use the function, enter
=ParseName(cell_ref, Index)
The "Index Codes" are listed in the VBA code.
If you need to add more salutations to be excluded, add them to sTitle
extending the pipe-delimited string you see there.
To enter the UDF, <alt-Fll> opens the VBEditor. Ensure your project is
highlighted in the project explorer window, then Insert/Module and paste the
code below into the window that opens.
========================================
Option Explicit
Function ParseName(str As String, Index As Long) As String
Dim re As Object
Dim mc As Object
Dim sPat As String
Dim sTitle As String
'Index code
' 1 = Salutation
' 3 = First Name
'Pipe-delimited list of possible Titles
sTitle = "Mr|Ms|Miss|Mrs|Dr"
sPat = "^((" & sTitle & ")\.?(\s+))?(\w+)"
Set re = CreateObject("vbscript.regexp")
re.ignorecase = True
re.Pattern = sPat
If re.test(str) = True Then
Set mc = re.Execute(str)
ParseName = mc(0).submatches(Index)
End If
End Function
=====================================
--ron
