Slightly Different Extracting Text from a Cell

[Wendy L]

Windows XP
Office XP

Okay I already posted a similar question once but my needs have slightly
changed.

If i have a text string in a cell, I want to extract all text to the left of
the "space character" (" ") that is furthest to the right.

So if my address line is:
Stanton, Ohio 79541-6598 - the formula will extract the text "Stanton, Ohio"

Thanks so much for all your help.

 

[Guest]

=LEFT(A1,FIND(" ",A1,FIND(", ",A1)+2))

This is assuming you're always going to have the ", " separating the town
and state. If that assumption changes, modify the nested FIND to reflect the
changes.

Good luck!
Elma

 

[Frank Kabel]

Hi
try
=TRIM(LEFT(A1,VLOOKUP(2,1/(MID(A1,INDIRECT(ROW("1:1024")),1)="
"),INDIRECT(ROW("1:1024")))-1))

 

[Dave Peterson]

That line break position was unfortunate and I think Frank had a couple of
typos:

=TRIM(LEFT(A1,LOOKUP(2,1/(MID(A1,ROW(INDIRECT("1:1024")),1)=" "),
ROW(INDIRECT("1:1024")))-1))
(all one cell)

 

[Frank Kabel]

Hi Dave
thanks for the correction!!