Separate first name, middle name and last name

[NoviceUser]

Hello - My worksheet has a range of names (not similar), where some have
first, mid and last names, some have first and last names, first, last and
suffix etc. I came across a tutorial that helped me separate first, mid and
last name, but the forumula doesn't work if there is no middle name.. I want
to create a macro so I don't have to manually do a text-to-column every time.

Sample data:

Eric M Barnes
Edward L Bassard III
Aurora Sofia Garcia
Angela N Higginbotham
Amanda Marie Roccuzzo
Brent Murakami


Forumla tried -
Left: =LEFT(A3, SEARCH(" ",A3,1)) --- works good

Mid: =MID(A2,SEARCH(" ",A2,1)+1,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)-SEARCH("
",A2,1)) ------- works only if there is a middle initial, else returns a
#VALUE.

Right =RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2,1)+1)) ---- doesn't work
if I use the Left and Mid formula, but there is NO middle name/initial.

Please assist ASAP.

Thanks!

 

[Rik_UK]

For mid try

=IF(ISERROR(SEARCH(" ",A2,SEARCH(" ",A2,1)+1)),"",MID(A2,SEARCH("
",A2,1)+1,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)-SEARCH(" ",A2,1)))

for right try

=IF(ISERROR(SEARCH(" ",A2,SEARCH(" ",A2,1)+1)),RIGHT(A2,LEN(A2)-SEARCH("
",A2,1)),RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2,1)+1)))

--
If this is the answer you hoped for please remember to click the yes button
below...

Kind regards

Rik

 

[Ron Rosenfeld]

Hello - My worksheet has a range of names (not similar), where some have
first, mid and last names, some have first and last names, first, last and
suffix etc. I came across a tutorial that helped me separate first, mid and
last name, but the forumula doesn't work if there is no middle name.. I want
to create a macro so I don't have to manually do a text-to-column every time.

Sample data:

Eric M Barnes
Edward L Bassard III
Aurora Sofia Garcia
Angela N Higginbotham
Amanda Marie Roccuzzo
Brent Murakami


Forumla tried -
Left: =LEFT(A3, SEARCH(" ",A3,1)) --- works good

Mid: =MID(A2,SEARCH(" ",A2,1)+1,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)-SEARCH("
",A2,1)) ------- works only if there is a middle initial, else returns a
#VALUE.

Right =RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2,1)+1)) ---- doesn't work
if I use the Left and Mid formula, but there is NO middle name/initial.

Please assist ASAP.

Thanks!

This macro will parse the cells that you select into the three adjacent
columns.

The First Name is the first space-delimited word in the string.
The Last Name is the last word in the string, UNLESS it is followed by one of
the suffixes that are in the pipe-delimited list of sPat, in which case it
would be the next-to-last word.

The Middle Name is anything in between the first and last names.

As written, the names must consist of letters and/or hyphens.


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.

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

=============================================
Option Explicit
Sub ParseName()
Dim c As Range, rg As Range
Dim s As String
Dim re As Object, mc As Object
Const sPat As String = _
"^(\b[A-Z-]+\b)\s*([\sA-Z-.]+)?\s+(\b[A-Z-]" _
& "+\b)\s*(Jr|Sr|III|II|IV|MD|M\.D\.|PhD\.?)?\s*$"

Set re = CreateObject("vbscript.regexp")
re.Pattern = sPat
re.ignorecase = True

Set rg = Selection 'although could set rg differently
Range(rg.Offset(0, 1), rg.Offset(0, 3)).ClearContents

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