formula to reverse first and last names

[biglb79]

I have a spreadsheet showing names of individuals with first name then last name separated by a space. is there a formula I can use to have last name, first name?

so last name then a comma and first name

 

[MARK858]

What version of Excel are you using and are there any middle names or letters

 

[biglb79]

What version of Excel are you using and are there any middle names or letters

no middle initials. it's just a first name (space) last name

Excel 2019 MSO

 

[Fluff]

Excel 2019 MSO

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[biglb79]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

done

 

[MARK858]

Try...

Book1
	A	B
1	SP Name
2	Andrew Fuller	Fuller, Andrew
3	Anne Dodsworth	Dodsworth, Anne
4	Laura Callahan	Callahan, Laura
5	Margaret Peacock	Peacock, Margaret
6	Janet Leverling	Leverling, Janet
7	Robert King	King, Robert
8	Nancy Davolio	Davolio, Nancy
9	Michael Suyama	Suyama, Michael
Sheet3

Cell Formulas
Range	Formula
B2:B9	B2	=RIGHT(A2,LEN(A2)-FIND(" ",A2)) & ", " & LEFT(A2, FIND(" ", A2)-1)

 

[Sergius]

Hello! How about this?

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

 

[Joe4]

Do you have any multi-word first names like "Billy Joe" or "Bobby Sue"?
Or any multi-row last names like "Van Buren"?

If so, then this can be a bit tricky, because then there could be multiple spaces in the name, and Excel would have no idea which one separates the first name from the last name (as you cannot come up with any sort of "rule" or "logic" that is guaranteed to work in all instances).

 

[biglb79]

Try...

Book1
	A	B
1	SP Name
2	Andrew Fuller	Fuller, Andrew
3	Anne Dodsworth	Dodsworth, Anne
4	Laura Callahan	Callahan, Laura
5	Margaret Peacock	Peacock, Margaret
6	Janet Leverling	Leverling, Janet
7	Robert King	King, Robert
8	Nancy Davolio	Davolio, Nancy
9	Michael Suyama	Suyama, Michael
Sheet3

Cell Formulas
Range	Formula
B2:B9	B2	=RIGHT(A2,LEN(A2)-FIND(" ",A2)) & ", " & LEFT(A2, FIND(" ", A2)-1)

perfect, thanks!

 

[MARK858]

Your welcome, happy we could help