Separate first name, middle name and last name
 

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!

Separate first name, middle name and last name
 

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


"NoviceUser" wrote:

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!