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?

Hi!

Try this:

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

Assumes the format is always the same:

Name<spaces,<spaceName

Biff

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

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

"Biff" wrote in message
...
Hi!

Try this:

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

Assumes the format is always the same:

Name<spaces,<spaceName

Biff

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

Biff wrote:

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

fred Wrote:
Biff wrote:

=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"
Hi Fred,

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

oldchippy


--
oldchippy
------------------------------------------------------------------------
oldchippy's Profile: http://www.excelforum.com/member.php...o&userid=19907
View this thread: http://www.excelforum.com/showthread...hreadid=569115

oldchippy wrote:

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

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

"fred" wrote...

oldchippy wrote:

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

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

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 :)


--
oldchippy
------------------------------------------------------------------------
oldchippy's Profile: http://www.excelforum.com/member.php...o&userid=19907
View this thread: http://www.excelforum.com/showthread...hreadid=569115

oldchippy wrote:
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!

oldchippy wrote:
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

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