Formula for Names

[menor59]

Hello Folks,

In My Work Book I have:

James Brown
Han Solo
Luke Skywalker

I need a formula that will make it :

Brown, James
Solo, Han
Skywalker, Luke

Your thought please and Thank you in advance

 

[Joe4]

One way:

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

 

[hiker95]

menor59,

And, another way:

If cell A2 contains the following:
James Brown

In cell B2 copy the following formula:
=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),LEN(A2)))&", "&LEFT(A2,FIND(" ",A2)-1)

And, cell B2 will display the following:
Brown, James

 

[admiral100]

And another way...

=MID(REPT(A1&", ",2),FIND(" ",A1)+1,LEN(A1)+1)

 

[jtakw]

Hi,

Plenty of choices already for FIRST LAST name.

Assuming Only last word is LAST name, in case you have middle names and/or initials:

Book1
	A	B
1	James Brown	Brown, James
2	Han Solo	Solo, Han
3	Luke Skywalker	Skywalker, Luke
4	James T Kirk	Kirk, James T
5	Sarah Michelle Gellar	Gellar, Sarah Michelle
Sheet123

Cell Formulas
Range	Formula
B1	=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),100))&", "&TRIM(SUBSTITUTE(A1,TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),100)),""))

 

[Eric W]

There's a non formula way also. Given your list in A, type the desired result of A1 in B1. Then select B1 and press Contol-E (Flash Fill). Excel will fill in the rest of the rows. If you have middle initials or multi-word names like jtakw showed, select one of the rows with the middle initial, put the desired result in column B (B4 or B5 in that example), then select B4 and press Control-E. Excel's pattern matching routines are often as good as a formula, but like formulas, they don't always get it right when there's something a little different.