parsing a full name out into Last, First, Middle

[Craig]

Hi,

I have many full names separated by spaces in many rows of column A. Some
full names have middle names or middle initials while some have a comma at
the end of their name, followed by a job title...if there is a comma at the
end of their name, then it is followed by a job title.

I want the last name of each person separated out of col A and displayed in
col B of the same row, the first name separated out of col A and displayed in
col C of the same row, the middle initial or middle name separated out of col
A and displayed in col D of the same row OR col D would be blank if the name
in col A does not have a middle initial or name, and the job title separated
out of col A and displayed in Col E of the same row OR col E would be blank
if the name in col A does not have a job title.

For example, I need a function that can separate out full names in column A
cells such as:

"John Smith" in cell A1 into 2 separate cells with "Smith" in B1 and "John"
in C1 with nothing in cell D1 as there is no Middle name or middle initial
and nothing in cell E1 as there is no job title

"John Q. Smith" in cell A2 into 3 separate cells with "Smith" in B2 "John"
in C2 and "Q." in D2 with nothing in E2 as there is no job title

"John Quincy Smith" in cell A3 into 3 separate cells with "Smith" in B3
"John" in C3 and "Quincy" in D3 with nothing in E3 as there is no job title

"John Q. Smith, Exec. Dir." in cell A4 into 4 separate cells with "Smith" in
B4 "John" in C4 "Q." in D4 and "Exec. Dir." in E4

"John Q. Smith, Executive Director" in cell A5 into 4 separate cells with
"Smith" in B5 "John" in C5 "Q." in D5 and "Executive Director" in E5

"John Smith, Executive" in cell A6 into 3 separate cells with "Smith" in B6
"John" in C6 with nothing in D6 as there is no middle name or middle initial
and "Executive" in E6

Thank you very much for any assistance!

Craig

 

[Don Guillett]

http://www.cpearson.com/excel/FirstLast.htm

 

[Ron Rosenfeld]

I have many full names separated by spaces in many rows of column A. Some
full names have middle names or middle initials while some have a comma at
the end of their name, followed by a job title...if there is a comma at the
end of their name, then it is followed by a job title.

I want the last name of each person separated out of col A and displayed in
col B of the same row, the first name separated out of col A and displayed in
col C of the same row, the middle initial or middle name separated out of col
A and displayed in col D of the same row OR col D would be blank if the name
in col A does not have a middle initial or name, and the job title separated
out of col A and displayed in Col E of the same row OR col E would be blank
if the name in col A does not have a job title.

This cannot be done completely with full accuracy because of the tremendous
variability in names. If a person has just a single first name, and a single
last name, then it is possible.

But what about

Mary Ann June Foster Smith
Julio de la Cortez

And so forth.

In any event, here is a UDF that should do some of the work.

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

Alter the "Set rg =" line to reflect the range you wish to parse. The result
will appear adjacent.

To use this Macro (Sub), <alt-F8> opens the macro dialog box. Select the macro
by name, and <RUN>.

=======================================
Option Explicit
Sub ParseName()
Dim s As String
Dim rg As Range, c As Range
Dim re As Object, mc As Object, m As Object
Dim i As Long

Set rg = Range("A2:A10")
Set re = CreateObject("vbscript.regexp")
re.Pattern = "^(\S+)\s?(\S*)\s([^,]+)[,\s]*(.*)$"

For Each c In rg
Range(c.Offset(0, 1), c.Offset(0, 4)).ClearContents
s = c.Value
If re.test(s) Then
Set mc = re.Execute(s)
c.Offset(0, 1).Value = mc(0).submatches(2)
c.Offset(0, 2).Value = mc(0).submatches(0)
c.Offset(0, 3).Value = mc(0).submatches(1)
c.Offset(0, 4).Value = mc(0).submatches(3)
End If
Next c
End Sub
====================================
--ron