Separate then concatenat

[Peter Muller]

I have a list of names and surnames in Col A, and want to change the sequence to surname first, then a comma, then a space, then the first name. I presently use a roundabout way to get the result, and have col's B & C hidden. Please help me with a more simple/condensed formula

Name and Surname			Surname, Name
	=LEFT(A3, SEARCH(" ",A3,1))	=RIGHT(A3,LEN(A3)-SEARCH(" ",A3,1))	=CONCAT(C3,",", " ",B3)
Peter John	Peter	John	John, Peter
Abba Smith	Abba	Smith	Smith, Abba
Jerry Barry	Jerry	Barry	Barry, Jerry

 

[Radoslaw Poprawski]

here:

Book4
	A	B
1	Name and Surname	solution
2	Peter John	John, Peter
3	Abba Smith	Smith, Abba
4	Jerry Barry	Barry, Jerry
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=TRIM(RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1)))&", "&TRIM(LEFT(A2,SEARCH(" ",A2,1)))

 

[Peter Muller]

here:

Book4
	A	B
1	Name and Surname	solution
2	Peter John	John, Peter
3	Abba Smith	Smith, Abba
4	Jerry Barry	Barry, Jerry
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=TRIM(RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1)))&", "&TRIM(LEFT(A2,SEARCH(" ",A2,1)))

Thank you so much, this is perfect

 

[Radoslaw Poprawski]

ur welcme
Mark as answered please