Right Function

sbessette

New member
Joined
Feb 23, 2012
Messages
1
Reaction score
0
Points
0
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)

 

CheshireCat

New member
Joined
Dec 30, 2011
Messages
121
Reaction score
0
Points
0
Location
Victoria, Canada
Excel Version(s)
Microsoft Excel 2013
For short addresses or blank cells, you can avoid VALUE errors with:

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

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
Another way

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