Help with Nesting two functions

[fred]

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]

Hi!

Try this:

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

Assumes the format is always the same:

Name<spaces>,<space>Name

Biff

 

[oldchippy]

Hello Fred,

Try this,

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

Let me know if sucessful

oldchippy

 

[Biff]

Correction:

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

This is much better:

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

Biff

 

[fred]

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

Assumes the format is always the same:

well "John , Smith" is not the only name, and typing mistakes will be
diferent...
"Jamie, Jones" "Mickey ,Mouse"

 

[oldchippy]

well "John , Smith" is not the only name, and typing mistakes will
be
diferent...
"Jamie, Jones" "Mickey ,Mouse"

Hi Fred,

Have you tried the one I sent you, that takes care of extra "spaces"

oldchippy

 

[fred]

Hello Fred,

Try this,

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

Let me know if sucessful

Thanks man, it works!

 

[fred]

...
Have you tried the one I sent you, that takes care of extra "spaces"
...

I did, yours worked, Biff's didn,t

 

[fred]

oops I didnt notice the (C2), I want everything to be taken from Col
A
Any new change could help?

 

[Biff]

I did, yours worked, Biff's didn,t

Well, that's what happens when there's only a single sample to work with.

This eliminates the need for the intermediate formula in column C:

=TRIM(MID(A2,FIND(",",A2)+1,255))&" "&TRIM(LEFT(A2,FIND(",",A2)-1))

Also, assumes that there is a comma in each entry. See, that's the problem
"we" have to deal with when answering posts. Since most posters don't always
provide *ALL* the details "we" have to assume a lot of things! "We" can try
to account for "every possible situation" but then that often leads to
overkill and unnecessarily long and overly complex solutions.

Biff

 

[fred]

...

Well, that's what happens when there's only a single sample to work with.

This eliminates the need for the intermediate formula in column C:

=TRIM(MID(A2,FIND(",",A2)+1,255))&" "&TRIM(LEFT(A2,FIND(",",A2)-1))

Also, assumes that there is a comma in each entry. See, that's the problem
"we" have to deal with when answering posts. Since most posters don't always
provide *ALL* the details "we" have to assume a lot of things! "We" can try
to account for "every possible situation" but then that often leads to
overkill and unnecessarily long and overly complex solutions.

Biff

Good Job Biff, Im sorry for the misunderstandings, thanks for following
up the discusion, I'll have that in mind next time.

 

[Biff]

You're welcome. Thanks for the feedback!

Biff

Good Job Biff, Im sorry for the misunderstandings, thanks for following
up the discusion, I'll have that in mind next time.