I like this formula (copied from Peo Sjoblom, IIRC) since the ^^ characters
don't usually appear in cells. Dots/periods appear much more often.

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

Len(a1)-LEN(SUBSTITUTE(A1," ",""))
Will tell you how many spaces are in A1.

Say a1 contains:
Bill & Susan Vilbert
=len(a1) returns 20
=LEN(SUBSTITUTE(A1," ",""))
returns 17
doing the subtraction returns 3.

So now the formula looks like:

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

This portion:
SUBSTITUTE(A1," ", "^^",3)
returns:
"Bill & Susan^^Vilbert"
It replaced that 3rd (aka last) occurance with ^^.

The Find() will return 13 so the formula looks like:
=RIGHT(A1,LEN(A1)-13)

=======
Depending on the version of excel you're using, you can use:
Select the cell with the formula
tools|formula auditing|Evaluate formula
And step through each piece of the formula.
(IIRC, xl2002 added this.)

In earlier versions, you can highlight portions of the formula (in the formula
bar) and hit F9 to see how it evaluates.

Hit ctrl-z to revert
or edit|undo after you've changed it too much.

clearwaterdave wrote:

Don Guillett Wrote:
=MID(TRIM(G5),FIND("·",SUBSTITUTE(TRIM(G5),"
","·",LEN(TRIM(G5))-LEN(SUBSTITUTE(G5," ",""))))+1,256)

--
Don Guillett
SalesAid Software

"clearwaterdave"
<clearwaterdave.2bmhvt_1154035508.7878@excelforu m-nospam.com wrote in
message
news:clearwaterdave.2bmhvt_1154035508.7878@excelfo rum-nospam.com...

Usually in a list of names, I frequently need to find the last word
in a
field, i.e., locate the last space. Is there any way of doing this
with
formulas alone or must I resort to VBA?


--
clearwaterdave

------------------------------------------------------------------------
clearwaterdave's Profile:
http://www.excelforum.com/member.php...o&userid=19365
View this thread:
http://www.excelforum.com/showthread...hreadid=565801


I have thoroughly digested this piece of art and understand how it
works -- ALMOST. Does anyone know the philosophy of how it works.
Using the string "Bill & Susan Vilbert", a 20 character string I have
tried to understand its workings. The second parm of the FIND field
seems to find the location of the last space but I don't understand
how. The first SUBSTITUTE assigns a "." to a location definded as the
string length minus the length of the string without any spaces. By my
calculations that would be 20 (string length) less 17 (string length
without spaces) giving 3. That would mean that the space located at
position 3 would be replaced by a period. In reality it is replacing
the space at position 13 with a peiod.

Since it is finding the right position and it is working, I have no
problem. It's just a slight mental block I have as to how it's finding
the 13th and not 3rd position.

What am I overlooking?

BY THE WAY Many, many thanks to all who helped me with this challenge.

--
clearwaterdave
------------------------------------------------------------------------
clearwaterdave's Profile: http://www.excelforum.com/member.php...o&userid=19365
View this thread: http://www.excelforum.com/showthread...hreadid=565801

--

Dave Peterson