Reversing Names and Removing commas and Middle Initital

[GWCollo]

Hi All

I am seeking a single formula to transpose name formate and remove comma and middle initial

Current State Example Cell J27
Simpson, Peter R
Van Berg, Robin M
De Boer, Peter B
O'Brien, Roger T

Desired State
Peter Simpson
Robin Van Berg
Peter De Boer
Roger O'Brien

Currently I can do this with two steps
Step 1 referencing J27: =MID(J27&" "&J27,FIND(", ",J27)+2,LEN(J27)-1)
Step 2 referencing D27: =LEFT(D27,FIND(" ",D27))&TRIM(RIGHT(SUBSTITUTE(D27," ",REPT(" ",99)),99))

Is there a way to combine the two steps or replace with a single step?

[janmorris]

try replacing every occurrence of "D27" with the formula that references J27.

the formula will end up being quite long.

[AliGW]

Like this:

=TRIM(LEFT(SUBSTITUTE(MID(A1,FIND(",",A1)+2,99)," ",REPT(" ",99)),98))&" "&LEFT(A1,FIND(",",A1)-1)

where A1 contains the name.