I need to split a name field in a column that contains one or all of the
following components without consistency: courtesy title (i.e. Mr.), First
Name, Middle Inital and Last Name. Some examples of records in my column are:
Mr. K. Kyle Mathis
Ms. Terri Mathis
Ms. Ellen E. Vallatini
Cole Vallatini
Nick A. Vallatini
I tried using the command "Data" "Text to Columns" but, with the
inconsitency in data, it split correctly in some place but incorrectly in
others. Any ideas?
Parsing names can be tough.
Especially when you don't indicate how you want the names you give parsed!
For Courtesy title, you need to list them all. And even then there may be
errors. For example, in the name M. James Cook, is the M. a courtesy title, or
is it the initial of the first name?
You state the components include FirstName, Middle Initial ...
What is the Middle Initial in your first example?
Mr. K. Kyle Mathis
Or maybe you mean something else?
If you want the name parsed into the fields you listed, should the above look
like:
Mr. | K. | K | Mathis
or do you want the middle name and not just the middle initial?
What if there are multiple middle name/initials?
What about certain foreign names?
In any event, this UDF should give you a start. It assumes that the title is
in pipe separated list you will see in the UDF. It also assumes that the first
word following is the first name; the last word is the last name; and
everything in-between is what you want for the Middle name.
If that is not what you want, you'll have to give more specifics.
You may find problems with certain names, but if you do, you'll have to give
more specifics.
Let me know.
To enter the UDF, <alt-F11> opens the VB Editor. Ensure your project is
highlighted in the project explorer window, then Insert/Module and paste the
code below into the window that opens.
To use it, enter a formula of the form =ParseName(cell_ref, Component) where
cell_ref refers to a single cell containing the full name; and Component is the
part of that name you want to extract (see the comments within the code for the
translation -- e.g 1 = title; 2=First Name; etc).
=======================================
Option Explicit
Function ParseName(Str As String, Component As Long) As String
'Component Key:
' 1 = Courtesy Title
' 2 = First Name
' 3 = Middle initial or Names
' 4 = Last Name
Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.Pattern = "^((Mr|Mrs|Ms|Dr)\.)?\s*(\S+)\s*(.*)?\s+(\S+)$"
If re.test(Str) = True Then
Set mc = re.Execute(Str)
With mc(0)
Select Case Component
Case Is = 1
ParseName = .submatches(0)
Case Is = 2
ParseName = .submatches(2)
Case Is = 3
ParseName = .submatches(3)
Case Is = 4
ParseName = .submatches(4)
End Select
End With
End If
End Function
=====================================
--ron
