29-Sep-06 06:35 PM

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

29-Sep-06 06:43 PM

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




--

Dave Peterson

29-Sep-06 06:55 PM

Thanks Dave - I have NO idea how this works, but it works

29-Sep-06 07:05 PM

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

29-Sep-06 07:10 PM

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

29-Sep-06 07:21 PM

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.





--

Dave Peterson

29-Sep-06 07:28 PM

BRILLIANT!! (Both of you)

Dave, could you please explain the use of "^^"?

29-Sep-06 08:19 PM

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

29-Sep-06 08:42 PM

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

29-Sep-06 09:18 PM

I get it now. Thanks again.

29-Sep-06 09:26 PM

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

The ^^ could show up in a cell.  But I do not recall seeing anyone use char(1) in
any worksheet.


--

Dave Peterson

30-Sep-06 12:27 PM

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

30-Sep-06 12:35 PM

Could have come from John Walkenbach, too.

He has an explanation of how to build the formula:
http://www.j-walk.com/ss/excel/usertips/tip079.htm




--

Dave Peterson

30-Sep-06 02:58 PM

It seems no one wants to believe me when I say that it came from Leo Heuser,
way back in 2001!

http://tinyurl.com/g2ca7

http://tinyurl.com/k9lzb


--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
as I
isn't

Excel - I believe you. I just don't remember it.

Asked By Dave Peterson

30-Sep-06 03:37 PM

I believe you.

I just do not remember it.  (And I still say that I got it from Peo!)


--

Dave Peterson