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
    Location
    Nanaimo, BC, Canada
    Posts
    2,250
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    =trim(left(a1,len(a1)-8))
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    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.

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

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

  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
  •