Slightly Different Extracting Text from a Cell

  • Thread starter Thread starter Wendy L
  • Start date Start date
W

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.
 
=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
 
Hi
try
=TRIM(LEFT(A1,VLOOKUP(2,1/(MID(A1,INDIRECT(ROW("1:1024")),1)="
"),INDIRECT(ROW("1:1024")))-1))
 
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)
 
Back
Top