help with functions to nest

[fher_182]

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?

 

[Trevor Shuttleworth]

Wherever you've got "C2" put "TRIM(SUBSTITUTE(A2,",",""))"

Regards

Trevor

 

[Niek Otten]

=RIGHT(TRIM(SUBSTITUTE(A2,",","")),LEN(TRIM(SUBSTITUTE(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

| 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?
|

 

[Biff]

See this same reply in .Misc:

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

Assumes the format is always the same:

Name<spaces>,<space>Name

Biff

 

[Biff]

Ooops!

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

Try this instead:

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

Biff