=RIGHT(TRIM(SUBSTITUTE(A2,",","")),LEN(TRIM(SUBSTI TUTE(A2,",","")))-FIND(" ",TRIM(SUBSTITUTE(A2,",",""))))&"
"&LEFT(TRIM(SUBSTITUTE(A2,",","")),SEARCH(" ",TRIM(SUBSTITUTE(A2,",","")))-1)

As you can see, this not an improvement. Maybe it can be tuned, but in general, having intermediate results in separate cells is
good for verifying correctness and doesn't hurt performance.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

wrote in message oups.com...
| Im using a formula like this to clean a name in COL A:
| =TRIM(SUBSTITUTE(A2,",",""))
| John , Smith
| Clean output in COL C is "John Smith"
|
| And I also use
| =RIGHT(C2,LEN(C2)-FIND(" ",C2))&" "&LEFT(C2,SEARCH(" ",C2)-1)
| to invert the name "John Smith" to "Smith John"
|
| What can I do to nest this two Formulas in only one step?
|