Right Function

[sbessette]

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)

[Ken Puls]

=trim(left(a1,len(a1)-8))

[CheshireCat]

For short addresses or blank cells, you can avoid VALUE errors with:

=TRIM(LEFT(A1,MAX(LEN(A1)-8,0)))

[Bob Phillips]

Another way

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