Help with Nesting two functions
 

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 Nesting two functions
 

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?

Help with Nesting two functions
 

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

Help with Nesting two functions
 

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?

Help with Nesting two functions
 

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"

Help with Nesting two functions
 

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

Help with Nesting two functions
 

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!

Help with Nesting two functions
 

oldchippy wrote:

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

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

Help with Nesting two functions
 

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?

Help with Nesting two functions
 

"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

Help with Nesting two functions
 

Biff wrote:
"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

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

Help with Nesting two functions
 

You're welcome. Thanks for the feedback!

Biff

"fred" wrote in message
oups.com...

Biff wrote:
"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

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