# Thread: Right Function

1. ## Right Function

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)

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

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

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

4. Another way

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•