How do I seperate a full name field out into three seperate columns?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rod - 31 Oct 2006 18:23 GMT

The name field is in the following format: Abbott, Susan H.

I would like to break it out into three seperate columns like below.

Last Name First Name Middle Initial
Abbott Susan H.

Reply to this Message

Gary Brown - 31 Oct 2006 18:58 GMT

Assume:
Full name (Abbott, Susan H.) is in cell A2,
First Name formula is in cell B2.
Last Name formula is in cell C2.
Middle formula Initial is in cell D2.

B2 =LEFT(A2,FIND("~",SUBSTITUTE($A2," ","~",1))-2)

C2 =IF(LEN(A2)-LEN(SUBSTITUTE(A2,"
",""))>=2,MID($A2,FIND("~",SUBSTITUTE($A2," ","~",1))+1,
FIND("~",SUBSTITUTE($A2," ","~",2))-FIND("~",SUBSTITUTE($A2,"
","~",1))-1),RIGHT($A2,LEN($A2)-FIND("~",SUBSTITUTE($A2," ","~",1))))

D2 =IF(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))>=2,RIGHT($A2,LEN($A2) -
FIND("~",SUBSTITUTE($A2," ","~",2))),"")

Watch the wrapping in this post.

Signature

HTH,
Gary Brown
gary.DeleteThis2SendMeAnEmail.Brown@kinneson.com
If this post was helpful to you, please select ''YES'' at the bottom of the
post.

> The name field is in the following format: Abbott, Susan H.
>
> I would like to break it out into three seperate columns like below.
>
> Last Name First Name Middle Initial
> Abbott Susan H.

Reply to this Message

Bob Phillips - 31 Oct 2006 19:59 GMT

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

=SUBSTITUTE(SUBSTITUTE(A1," "&D1,""),B1&", ","")

=MID(A1,FIND("~",SUBSTITUTE(A1," ",",~",LEN(A1)-LEN(SUBSTITUTE(A1,"
","")))),99)

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> The name field is in the following format: Abbott, Susan H.
>
> I would like to break it out into three seperate columns like below.
>
> Last Name First Name Middle Initial
> Abbott Susan H.

Reply to this Message

jahaglund@hotmail.com - 31 Oct 2006 21:06 GMT

I tried the following with good success:
With full name in A2
B2 =MID(A2,1,FIND(", ",A2,1)-1) for last name
C2 =MID(A2,FIND(" ",A2,1),FIND(" ",A2,1)-2) for first name
D2 =RIGHT(A2,2) for middle initial - could be a problem if there is no
middle initial

> The name field is in the following format: Abbott, Susan H.
>
> I would like to break it out into three seperate columns like below.
>
> Last Name First Name Middle Initial
> Abbott Susan H.

Reply to this Message