CODEWITHSUNDEEP
exceltextexcel-formulaworksheet-functionseparator
Botni
Botni

Reputation: 32

Separate Full String into Firstname, Middle Initial and Lastname by column

Using Excel, how can I separate a full name string, eg: 

    Juan I. Dela Cruz
    Jojo Rambo
    Unlce Sam
    Mary Ann J. San Jose

into output of 'Firstname','Middle Initial' and 'Lastname':

+----------+----------------+----------+
|Firstname | Middle Initial | Lastname |
+----------+----------------+----------+
|Juan      | I.             | Dela Cruz|
+----------+----------------+----------+
|Jojo      |                | Rambo    |
+----------+----------------+----------+
|Uncle     |                | Sam      |
+----------+----------------+----------+
|Mary Ann  | J.             | San Jose |
+----------+----------------+----------+

Upvotes: 0

Views: 12098

Answers (3)

Young Massens
Young Massens

Reputation: 1

After working on this for a couple of days, here is what I have come up with. This works on Excel 2000 as well. Seems to work well on a data set that I am using that is formatted as "Last Name", "First Name" "MI".

My Name Field is in Column B and my Formulas are in H, I, and J

Last Name

=LEFT(B2,SEARCH(",",B2)-1)

First Name

=IF(LEN(MID(B2,LEN(H2)+3,LEN(B2)-LEN(H2)))-LEN(SUBSTITUTE(MID(B2,LEN(H2)+3,LEN(B2)-LEN(H2))," ",""))>0,MID(B2,LEN(H2)+3,LEN(B2)-LEN(H2)-LEN(RIGHT(B2,LEN(B2)-FIND("|",SUBSTITUTE(B2," ","|",LEN(B2)-LEN(SUBSTITUTE(B2," ",""))))))-3),MID(B2,LEN(H2)+3,LEN(B2)-LEN(H2)))

Middle Inital

=IF(RIGHT(B2,1)=".",MID(B2,LEN(H2)+LEN(I2)+3,1),MID(B2,LEN(H2)+LEN(I2)+4,1))

So far this has handled any data that I can throw at it.

Good luck

Upvotes: 0

pnuts
pnuts

Reputation: 59460

In Firstname: =IFERROR(LEFT(A1,FIND(".",A1)-3),LEFT(A1,FIND(" ",A1)-1))
In Middle Initial =IFERROR(MID(A1,FIND(".",A1)-1,2),"")
In Lastname =IFERROR(RIGHT(A1,LEN(A1)-FIND(".",A1)-1),RIGHT(A1,LEN(A1)-FIND(" ",A1)))

each copied down to suit may be a start (for the examples provided) but, for the reasons as mentioned by @lurker, this is not likely to be a comprehensive solution.

Upvotes: 1

Mho
Mho

Reputation: 18

Initial thought is to use Text to Columns under the Data tab, with space as your delimiter. However the previous comment brings up a good point as to determining your names.

If you do not have many instances of names with several spaces, such as your Mary Ann J. San Jose example, then text to columns will work well - you can combine your outliers manually.

Upvotes: 0

Related Questions