Finding All but Last Word

[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

 

[Peo Sjoblom]

One way

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

 

[Guest]

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

 

[Guest]

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

 

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

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)

 

[Steve Madden]

I tried this but it returned #VALUE! instead of the last word.

 

[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

 

[Sandy Mann]

I also got a #VALUE error because when copied and pasted the line wrap
caused the space in the second SUBSTITUTE to be lost. Put the space in the
required place and it works.

--
HTH

Sandy
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Steve Madden]

That was it--thank you. I will remove from group in a couple of days.

 

[Krishnakumar]

=left(a1,lookup(len(a1),find(" ",a1,row($a$1:index($a:$a,len(a1)))))-1)