Formulas for Parsing Full names
 

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!

Formulas for Parsing Full names
 

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.


jonefer wrote:
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!

Formulas for Parsing Full names
 

On Tue, 14 Feb 2006 14:33:23 +1100, Govind wrote:

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

Formulas for Parsing Full names
 

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

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

in that case.

Regards

Govind.

Ron Rosenfeld wrote:
On Tue, 14 Feb 2006 14:33:23 +1100, Govind wrote:


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