Formulas for Parsing Full names

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Something I've done a lot, but never really took the time to write all the
steps (because it's usually easy enough)
is parsing out a Fullname: Doe, Mary Anne E. into 3 columns.
LastName, FirstName, MI

I started to make a little cheat sheet so that I could just blindly paste
the formulas into whatever worksheet I need to do this to, when it occurred
to me:

Has anyone already done this? (Created a perfect algorithm to catch every
scenario)
Certainly someone has!

I'm stuck with the example above: As you can see Mary Anne is her first name
and E is her middle initial which poses some problems for
the average name Doe, John E

Thanks!
 
Hi,

Lets say your name is in cell A1

Usethis formula in cell B1 to get the last name

=LEFT(A1,FIND(",",A1)-1)

Use this in C1 to get the First name

=MID(A1,FIND(",",A1)+2,(FIND("^",SUBSTITUTE(A1,"
","^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-(FIND(",",A1)+1)))

and use this in D1 to get the Middle Initial

=RIGHT(A1,1)

This would work in both the cases you had mentioned.

Regards

Govind.
 
Hi,

Lets say your name is in cell A1

Usethis formula in cell B1 to get the last name

=LEFT(A1,FIND(",",A1)-1)

Use this in C1 to get the First name

=MID(A1,FIND(",",A1)+2,(FIND("^",SUBSTITUTE(A1,"
","^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-(FIND(",",A1)+1)))

and use this in D1 to get the Middle Initial





=RIGHT(A1,1)
Click to expand...

In the first example (Doe, Mary Anne E.), this gives a dot as a result ('.')
This would work in both the cases you had mentioned.

Regards

Govind.
Click to expand...

--ron
 
Thanks for pointing it out Ron. I would slightly revise my formula to

=RIGHT(SUBSTITUTE(A1,".",""),1)

in that case.

Regards

Govind.
 
Back
Top