Amend Formula for Manipulating Names

[NorthbyNorthwest]

Hi, everyone. Hoping someone can help me with a formula. I found the following formula for manipulating names.

=LEFT(TRIM(MID(A1,SEARCH(",",A1)+1,255)),SEARCH(" ",TRIM(MID(A1,SEARCH(",",A1)+1,255)))-1)&" "&LEFT(A1,SEARCH(",",A1)-1)

The formula extracts last and first names from string, remove comma between, and reverses name to first and last names. Almost exactly what I want. The only problem I have with the formula is it returns a #VALUE! error if there is no middle name or initial, suffix like Jr., or remarks right of the last and first names. See table below.

Anderson, Donald L.	Donald Anderson
Brown, Stephanie Marie	Stephanie Brown
Evans, Lisa	#VALUE!​
Harris, Mark Jr.	Mark Harris
Smith-Jones, Barbara Ann	Barbara Smith-Jones
Van Patten, Richard retiring next month	Richard Van Patten

How can this formula be amended to address situation where there is only a last name, first name?

 

[Cubist]

Try:

Book1
	A	B
1	Anderson, Donald L.	Donald Anderson
2	Brown, Stephanie Marie	Stephanie Brown
3	Evans, Lisa	Lisa Evans
4	Harris, Mark Jr.	Mark Harris
5	Smith-Jones, Barbara Ann	Barbara Smith-Jones
6	Van Patten, Richard retiring next month	Richard Van Patten
Sheet4

Cell Formulas
Range	Formula
B1:B6	B1	=LET(ta,TEXTAFTER(A1,", "),TEXTBEFORE(ta," ",,,,ta) & " "& TEXTBEFORE(A1,","))

 

[NorthbyNorthwest]

Perfect! Thanks so much for the quick response. Take care.