Rearranging text (I screwed up the first post)

[needhelpwithonething]

I need a formula to change the way a person's name is displayed.
The current format is - Smith, Bob J.
And I need a formula to change it to - Bob J. Smith
I have a large list of names of different character lengths and some have the middle initial followed by a . and some do not have a middle inital.

Thank you

 

[rollis13]

I'm supposing that all cells have a comma after the surname and the list starts in cell A1; if so then you can use this formula in a help column, lets say B1 (then pull down):

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

 

[KRice]

A similar approach...and I assumed the same thing...that a comma can be used as a delimiter. I wasn't sure whether any extra spaces might follow the original name, so I wrapped that portion with TRIM.

MrExcel_20231214.xlsx
	A	B
1	Smith, Bob J.	Bob J. Smith
Sheet2

Cell Formulas
Range	Formula
B1	B1	=CONCAT(TRIM(RIGHT(A1,LEN(A1)-FIND(",",A1)))," ",LEFT(A1,FIND(",",A1)-1))

Note that the offering from @rollis13 has the name parts out of order...I believe it would be:

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

 

[rollis13]

OMG . Thanks @KRice, you're absolutely right, I was testing the single parts then I messed up the final merge .

 

[Rick Rothstein]

This somewhat simple formula should work for you...

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

 

[rollis13]

@Rick Rothstein , how about wordy surnames ?

 

[Rick Rothstein]

@Rick, how about wordy surnames ?

I am not sure what you mean... what is a "wordy surname"?

 

[rollis13]

Smith Taylor, Bob J. would be a 'wordy surname'.

 

[Rick Rothstein]

Smith Taylor, Bob J. would be a 'wordy surname'.

Okay, this modification should handle those...

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