On Tue, 28 Jul 2009 14:06:01 -0700, Craig
wrote:

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