Seperating last name and first name

[nnadimi19]

I have a column that has the last name and first name together. How do I go about creating two columns with the last name and first name seperately.

Please see the example.

html.xls
	A	B	C	D	E
1			LastName	FirstName	Honorarium
2	Aronoff,Phillip,M.D.		Aronoff	Phillip	M.D.
3	Myint,Christina,M.D.		Myint	Christina	M.D.
4	Ramchandra,Mahalakshmi		Ramchandra	Mahalakshmi
5	Anderson,Viola		Anderson	Viola
6	Iyer,Hemalatha		Iyer	Hemalatha
7	Konikowski,Janusz		Konikowski	Janusz
8	Le,Hung		Le	Hung
Sheet2

 

[VN]

See here..


http://cpearson.com/excel/FirstLast.htm




---------------------------
VN.

 

[Krishnakumar]

Hi,

Book2
	A	B	C	D	E
1			Last Name	First Name	Honorarium
2	Aronoff, Phillip, M.D.		Aronoff	Phillip	M.D.
3	Myint, Christina, M.D.		Myint	Christina	M.D.
4	Ramchandra, Mahalakshmi		Ramchandra	Mahalakshmi
5	Anderson, Viola		Anderson	Viola
6	Iyer, Hemalatha		Iyer	Hemalatha
7	Konikowski, Janusz		Konikowski	Janusz
8	Le, Hung		Le	Hung
Sheet1

Formula in C2 and copied down,

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

In D2 and copied down,

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,C2,""),E2,""),",",""))

In E2 and copied down,

=IF(LEN(A2)-LEN(SUBSTITUTE(A2,",",""))>1,REPLACE(A2,1,LOOKUP(9.99999E+307,FIND(",",A2,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A2)))))+1,""),"")

HTH

 

[tactps]

Why not use Text to Columns from the Data menu? It looks like there is a comma and space between each section.

 

[bosco_yip]

Select C2 >> Insert >> Name >> Define

Defined workbook name : BreakString

Refer to Box, enter formula :

=EVALUATE("{"""&SUBSTITUTE($A2,",",""",""")&"""}")

C2 , enter formula and copied across to E2, then copied down :

=IF((COLUMN(A:A)=3)*(COLUMNS(BreakString)=2),"",INDEX(BreakString,COLUMN(A:A)-(COLUMNS(BreakString)=1)))

 

[thunderfoot]

Why not use Text to Columns from the Data menu? It looks like there is a comma and space between each section.

...... and then run the TRIM function to make sure all unwanted "spaces" are removed. :wink:

 

[nnadimi19]

Thank you all for the response