=trim(left(a1,len(a1)-8))
I have 2 rows of cells, each cell contains street address, city, state and zip.
890 West North Street, Norwich, VT 03152 902123 Northwest Street, Westerly, RI 02891
If I use the Right formula I can extract out the zip =RIGHT(A1,5), ok
then I can extract out the state =RIGHT(A1, 2)
Now here's the question
As cities have different lengths of characters, how can I extract out the data that is from the right up to where there is a space?
890 West North Street, Norwich 902123 Northwest Street Westerly
Do not assume that there is always a comma (if there was I could use the text to column feature)
=trim(left(a1,len(a1)-8))
Ken Puls, FCPA, FCMA, MS MVP
Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!
Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter
If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.
For short addresses or blank cells, you can avoid VALUE errors with:
Code:=TRIM(LEFT(A1,MAX(LEN(A1)-8,0)))
Another way
=LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1))-2)
Bookmarks