help with functions to nest

[fher_182@hotmail.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?

[Trevor Shuttleworth]

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

Regards

Trevor


<fher_182@hotmail.com> wrote in message
news:1154976421.947129.284840@b28g2000cwb.googlegroups.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?
>

[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

<fher_182@hotmail.com> wrote in message news:1154976421.947129.284840@b28g2000cwb.googlegroups.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?
|

[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

<fher_182@hotmail.com> wrote in message
news:1154976421.947129.284840@b28g2000cwb.googlegroups.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?
>

[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

"Biff" <biffinpitt@comcast.net> wrote in message
news:%23nNmdSluGHA.2448@TK2MSFTNGP06.phx.gbl...
> 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
>
> <fher_182@hotmail.com> wrote in message
> news:1154976421.947129.284840@b28g2000cwb.googlegroups.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?
>>
>
>