Formulas for Parsing Full names

[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!

 

[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)

This would work in both the cases you had mentioned.

Regards

Govind.

 

[Ron Rosenfeld]

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)

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.

--ron

 

[Govind]

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

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

in that case.

Regards

Govind.