"Rick Rothstein (MVP - VB)" wrote...
....
=LEFT(MID(TRIM(A1),FIND(", ",TRIM(A1))+2,255),FIND(" ",MID(TRIM(A1),
FIND(", ",TRIM(A1))+2,255)))&REPLACE(LEFT(TRIM(A1),
FIND(", ",TRIM(A1))-1),1,FIND(" ",TRIM(A1),FIND(" ",TRIM(A1))+1),"")

That formula seems to still be dependent on token numbers...

Token numbers? You mean 255? It's an arbitrarily large number, at least
compared to the string length of individual names in most human languages,
and it's the 3rd argument to MID, effectively meaning get the rest of the
string. The other numbers, +1 and +2, move past the found substrings from
the FIND calls. Hardly dependencies.

Or do you mean assuming the name is the 3rd and 4th tokens combined, which
are separated by ", "?

. . . This formula **only** requires the last and first names to
be separated by the first comma-space pair in the string...

=MID(A1,2+FIND(", ",A1),FIND(" ",A1,2+FIND(", ",A1))-2
-FIND(", ",A1))&" "&REPLACE(SUBSTITUTE(LEFT(" "&A1,
1+FIND(", ",A1)-1)," ",CHAR(1),LEN(LEFT(A1,FIND(", ",A1)))
-LEN(SUBSTITUTE((LEFT(A1,FIND(", ",A1)))," ",""))+1),1,
FIND(CHAR(1),SUBSTITUTE(LEFT(" "&A1,1+FIND(", ",A1)-1)," ",CHAR(1),
LEN(LEFT(A1,FIND(", ",A1)))-LEN(SUBSTITUTE((LEFT(A1,FIND(", ",A1))),
" ",""))+1)),"")
....

If you want to use ", " as the indicator of where the name is, taking the
tokens to the left and right of it no matter where they appear in A1, and
swapping their order, use

=TRIM(MID(A1,FIND(", ",A1)+2,FIND(" ",TRIM(MID(A1,FIND(", ",A1)+2,
255)))))&MID(LEFT(A1,FIND(", ",A1)-1),FIND(", ",SUBSTITUTE(LEFT(A1,
FIND(", ",A1))," ",", ",FIND(", ",A1)-LEN(SUBSTITUTE(LEFT(A1,
FIND(", ",A1))," ","")))),255)