Results 1 to 4 of 4

Thread: Right Function

  1. #1

    Right Function

    Register for a FREE account, and/
    or Log in to avoid these ads!

    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. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Nanaimo, BC, Canada
    Blog Entries
    Excel Version
    Excel Office 365 Insider
    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: -||- Blog: -||- 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.

  3. #3
    Conjurer CheshireCat's Avatar
    Join Date
    Dec 2011
    Victoria, Canada
    Excel Version
    Microsoft Excel 2013
    For short addresses or blank cells, you can avoid VALUE errors with:


  4. #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