Split the Last and Last Name

[niladri20052006]

Hi All,

I have a list consisting of more than 50,000+ data where i have contact names as follows

Reed, Jehri
Marani, Richard
Patel, Malav

This is arranged as last name then comma and then First Name. But I want the exactly opposite. The first Name will be the first and then Last name. I want to use the formula as I have already used "Text to Column" option.

Thanks in advance!

 

[VoG]

Hi. Try

=RIGHT(A1,LEN(A1)-FIND(",",A1)-1)&" "&LEFT(A1,FIND(",",A1)-1)

 

[edokhotnik]

Hi All,

I have a list consisting of more than 50,000+ data where i have contact names as follows

Reed, Jehri
Marani, Richard
Patel, Malav

This is arranged as last name then comma and then First Name. But I want the exactly opposite. The first Name will be the first and then Last name. I want to use the formula as I have already used "Text to Column" option.

Thanks in advance!

If you want them into separate columns:

Excel Workbook
	A	B	C
1	FullName	FirstName	LastName
2	Reed, Jehri	Jehri	Reed
3	Marani, Richard	Richard	Marani
4	Patel, Malav	Malav	Patel
Sheet1

Excel 2010

Cell Formulas
Range	Formula
B2	=RIGHT(A2,LEN(A2)-FIND(" ",A2))
B3	=RIGHT(A3,LEN(A3)-FIND(" ",A3))
B4	=RIGHT(A4,LEN(A4)-FIND(" ",A4))
C2	=LEFT(A2,FIND(",",A2)-1)
C3	=LEFT(A3,FIND(",",A3)-1)
C4	=LEFT(A4,FIND(",",A4)-1)

 

[niladri20052006]

Hi VoG,

Thanks . It is working fine.

However I have got another one also

=REPLACE(D2,1,FIND(",",D2)+1,"")&""&LEFT(D2,FIND(",",D2)-1)

 

[Peter_SSs]

Hi VoG,

Thanks . It is working fine.

However I have got another one also

=REPLACE(D2,1,FIND(",",D2)+1,"")&""&LEFT(D2,FIND(",",D2)-1)

Presumably you do have a space in the red bit.

=REPLACE(A1,1,FIND(",",A1)+1,"")&" "&LEFT(A1,FIND(",",A1)-1)


Here's a simpler one:

=MID(D2&" "&D2,FIND(" ",D2)+1,LEN(D2)-1)