Help with Nesting two functions

  • Thread starter Thread starter fred
  • Start date Start date
F

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?
 
Hi!

Try this:

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

Assumes the format is always the same:

Name<spaces>,<space>Name

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 :)
 
Biff said:
=MID(A1&" "&A1,FIND(",",A1)+2,LEN(A1)-1)

Assumes the format is always the same:
Click to expand...
well "John , Smith" is not the only name, and typing mistakes will be
diferent...
"Jamie, Jones" "Mickey ,Mouse"
 
fred said:
well "John , Smith" is not the only name, and typing mistakes will
be
diferent...
"Jamie, Jones" "Mickey ,Mouse"
Click to expand...
Hi Fred,

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

oldchippy
 
oldchippy said:
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
Click to expand...


Thanks man, it works!
 
oops I didnt notice the (C2), I want everything to be taken from Col
A
Any new change could help?
 
I did, yours worked, Biff's didn,t
Click to expand...

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
 
Biff said:
...

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
Click to expand...

Good Job Biff, Im sorry for the misunderstandings, thanks for following
up the discusion, I'll have that in mind next time.
 
You're welcome. Thanks for the feedback!

Biff

fred said:
Good Job Biff, Im sorry for the misunderstandings, thanks for following
up the discusion, I'll have that in mind next time.
Click to expand...
 
Back
Top