Finding All but Last Word

S

Steve Madden

I work with street names. I know how to find the suffix or last word in a
cell, but I want to find all EXCEPT the last word in a cell. For example, if
cell A2 contained the Name Orchard Trail Rd, I would like a formula that
would return Orchard Trail. If the street name was Duke of Gloucester
Drive, I would like the formula to return Duke of Gloucester.

TIA
 
P

Peo Sjoblom

One way

=LEFT(A1,FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))-1)
 
R

Ron Coderre

Try this:
For A1 containing the text
B1: =LEFT(A1,LEN(A1)-MATCH("
",MID(A1,LEN(A1)+1-ROW(INDIRECT("1:"&(LEN(A1)))),1),0))
note: commit that array formula by holding down [Ctrl]+[Shift] and press
[Enter]

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro
 
R

Ron Coderre

Here's a non-array formula that also works:

B1: =LEFT(A1,SEARCH("^^",SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))-1)


***********
Regards,
Ron

XL2002, WinXP-Pro
 
H

Harlan Grove

Steve Madden wrote...
I work with street names. I know how to find the suffix or last word in a
cell, but I want to find all EXCEPT the last word in a cell. For example, if
cell A2 contained the Name Orchard Trail Rd, I would like a formula that
would return Orchard Trail. If the street name was Duke of Gloucester
Drive, I would like the formula to return Duke of Gloucester.
Click to expand...

If you have the last word of a string, which I'll denote LW, then the
portion to the left of it would just be

=LEFT(s,LEN(s)-LEN(LW)-1)

Without the last word, you could define a name like seq referring to a
formula like

=ROW(INDEX($1:$65536,1,1):INDEX($1:$65536,256,1))

which would evaluate to an array of sequential integers, then use a
formula like

=LEFT(s,LOOKUP(2,1/(MID(s,seq,1)=" "),seq)-1)
 
G

Gord Dibben

Steve

Works for me with the two examples you provided.

Did you type in or copy Peo's formula correctly?

Is your data exactly as your examples indicate?

Do you have data in A1?

I get the #VALUE! error if no data to parse.


Gord Dibben MS Excel MVP
 
Top