software

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(quot;,quot;,A1)-1)

Use this in C1 to get the First name

=MID(A1,FIND(quot;,quot;,A1) 2,(FIND(quot;^quot;,SUBSTITUTE(A1,quot;
quot;,quot;^quot;,LEN(A1)-LEN(SUBSTITUTE(A1,quot; quot;,quot;quot;))))-(FIND(quot;,quot;,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:
gt; Something I've done a lot, but never really took the time to write all the
gt; steps (because it's usually easy enough)
gt; is parsing out a Fullname: Doe, Mary Anne E. into 3 columns.
gt; LastName, FirstName, MI
gt;
gt; I started to make a little cheat sheet so that I could just blindly paste
gt; the formulas into whatever worksheet I need to do this to, when it occurred
gt; to me:
gt;
gt; Has anyone already done this? (Created a perfect algorithm to catch every
gt; scenario)
gt; Certainly someone has!
gt;
gt; I'm stuck with the example above: As you can see Mary Anne is her first name
gt; and E is her middle initial which poses some problems for
gt; the average name Doe, John E
gt;
gt; Thanks!
gt;
gt;
gt;

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

gt;Hi,
gt;
gt;Lets say your name is in cell A1
gt;
gt;Usethis formula in cell B1 to get the last name
gt;
gt;=LEFT(A1,FIND(quot;,quot;,A1)-1)
gt;
gt;Use this in C1 to get the First name
gt;
gt;=MID(A1,FIND(quot;,quot;,A1) 2,(FIND(quot;^quot;,SUBSTITUTE(A1, quot;
gt;quot;,quot;^quot;,LEN(A1)-LEN(SUBSTITUTE(A1,quot; quot;,quot;quot;))))-(FIND(quot;,quot;,A1) 1)))
gt;
gt;and use this in D1 to get the Middle Initial
gt;gt;=RIGHT(A1,1)

In the first example (Doe, Mary Anne E.), this gives a dot as a result ('.')

gt;
gt;This would work in both the cases you had mentioned.
gt;
gt;Regards
gt;
gt;Govind.
gt;

--ron

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

=RIGHT(SUBSTITUTE(A1,quot;.quot;,quot;quot;),1)

in that case.

Regards

Govind.

Ron Rosenfeld wrote:
gt; On Tue, 14 Feb 2006 14:33:23 1100, Govind gt; wrote:
gt;
gt;
gt;gt;Hi,
gt;gt;
gt;gt;Lets say your name is in cell A1
gt;gt;
gt;gt;Usethis formula in cell B1 to get the last name
gt;gt;
gt;gt;=LEFT(A1,FIND(quot;,quot;,A1)-1)
gt;gt;
gt;gt;Use this in C1 to get the First name
gt;gt;
gt;gt;=MID(A1,FIND(quot;,quot;,A1) 2,(FIND(quot;^quot;,SUBSTITUTE(A1 ,quot;
gt;gt;quot;,quot;^quot;,LEN(A1)-LEN(SUBSTITUTE(A1,quot; quot;,quot;quot;))))-(FIND(quot;,quot;,A1) 1)))
gt;gt;
gt;gt;and use this in D1 to get the Middle Initial
gt;gt;
gt;
gt;
gt;
gt;
gt;
gt;
gt;gt;=RIGHT(A1,1)
gt;
gt;
gt; In the first example (Doe, Mary Anne E.), this gives a dot as a result ('.')
gt;
gt;
gt;gt;This would work in both the cases you had mentioned.
gt;gt;
gt;gt;Regards
gt;gt;
gt;gt;Govind.
gt;gt;
gt;
gt;
gt; --ron