Find last word in a string

C

clearwaterdave

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?
 
D

Dave Peterson

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

Don Guillett

=MID(TRIM(G5),FIND("·",SUBSTITUTE(TRIM(G5),"
","·",LEN(TRIM(G5))-LEN(SUBSTITUTE(G5," ",""))))+1,256)
 
C

clearwaterdave

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

--
Don Guillett
SalesAid Software
[email protected]
"clearwaterdave"
message
Click to expand...


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.
 
D

Dave Peterson

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.
 
R

RagDyeR

Taking nothing away from Peo, I do believe this was originated by Leo Heuser
around the turn of the century.

This sticks in memory because Leo's original formula used the @ to denote
the indeterminate character, and this caused a lot of false links to appear
in formulas.
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

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.
 
B

Biff

This array formula eliminates the need for "marker characters":

=MID(A1,MAX((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=CHAR(32))*(ROW(INDIRECT("1:"&LEN(A1)))))+1,255)

My preference would be to use the "marker" method, though. It's not
volatile, isn't an array and is a few keystrokes shorter.

Biff
 
Top