NEED TO FIND FIRST " " FROM RIGHT

T

tootsuite

Help - I have a column in excel that contains names, such as:

Joe Smith
Joe E. Smith
Joe Edward Smith, esq.
etc

I need to isolate the LAST WORD in the column, for example:

Smith
Smith
esq.

The only way I know how to do this is to search for the first " " - but
I need to start the search from the RIGHT, not the LEFT. As far as I
know, FIND function starts the search from the right... which isn't
what I want.

Can someone please tell me how to accomplish this task.

THANK YOU
 
D

Dave Peterson

=RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)
-LEN(SUBSTITUTE(A1," ","")))))
 
T

tootsuite

Oops - I forgot to mention, I also need to isolate the "other" part of
the name:

Joe
Joe E.
Joe Edward Smith,

I can't really decipher your formula enough to extrapolate the reverse
of this.

Thanks
 
T

tootsuite

Actually, I figured it out. I just took the length of the last word,
then use LEFT function... thanks
 
D

Dave Peterson

I stole it from Peo Sjoblom. I'm not sure from whom he stole it <bg>.

but if you break it down into smaller pieces, it's not too bad.

Say you have "Joe Edward Smith, esq." in A1.

=substitute(a1," ","")
returns
JoeEdwardSmith,esq.
(all the spaces are gone)
=len(substitute(...)) will give 19

and len(a1) will give 22


Then
=SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))
is the same as:
=SUBSTITUTE(A1," ","^^",22-19)
or
=SUBSTITUTE(A1," ","^^",3)
So this says to replace the 3rd space character with ^^.

So
=SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))
is essentially just doing:


This portion:
=FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))
will return 18
since ^^ is the 18th (and 19th) character in that string:
Joe Edward Smith,^^esq.
(go ahead and count, I did!)

So it's really just saying:
=right(a1,len(a1)-18)
or
=right(a1,22-18)
or
=right(a1,4)
which is
esq.

Would I have come up with this?

Heck no! But lots of people have and I've stolen their answers.
 
T

tootsuite

I believe it is just a substitute character for the space - so that the
the last space, the one before the last word, is ^^, not " " - so as to
differentiate it from just a plain " "

me thinks anyways

BRILLIANT!! (Both of you)

Dave, could you please explain the use of "^^"?
Click to expand...
 
P

Pete_UK

Yeah, I think you are right - you are not likely to get that character
combination in any normal text. Dave often uses $$ in Find and Replace
operations, so I suppose if he hadn't "lifted" if from Peo, then you
might have found that combination of characters in the formula. <g>

Pete
 
T

Traveller

I get it now. Thanks again.

Pete_UK said:
Yeah, I think you are right - you are not likely to get that character
combination in any normal text. Dave often uses $$ in Find and Replace
operations, so I suppose if he hadn't "lifted" if from Peo, then you
might have found that combination of characters in the formula. <g>

Pete
Click to expand...
 
D

Dave Peterson

Actually, I think I would have used char(1).

The ^^ could show up in a cell. But I don't recall seeing anyone use char(1) in
any worksheet.
 
B

Bob Umlas

He either:
1) created it himself (likely)
2) stole it from me (also likely)
3) stole if from someone else (less likely)
 
Top