help with functions to nest
 

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?

help with functions to nest
 

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

Regards

Trevor


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?

help with functions to nest
 

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

help with functions to nest
 

See this same reply in .Misc:

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

Assumes the format is always the same:

Name<spaces,<spaceName

Biff

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?

help with functions to nest
 

Ooops!

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

Try this instead:

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

Biff

"Biff" wrote in message
...
See this same reply in .Misc:

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

Assumes the format is always the same:

Name<spaces,<spaceName

Biff

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?