Results 1 to 3 of 3
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post
    HI,

    I've attached a spreadsheet with 2 columns. The first column contains the address inluding the street or PO number. The user wants only the street name. I'll used the following formula but its not yielding the correct results. What am I missing?

    =RIGHT(A2,(LEN(A2)-1))

    Thanks,
    Leesha
    Attached Files Attached Files

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Use this array formula (enter using control+shift+enter):

    =IF(ISNUMBER(VALUE(LEFT(A2,1))),MID(A2,FIND(" ",A2),LEN(A2)),LEFT(A2,MAX(IF(ISERROR(FIND(" ",A2,ROW($1:$1000))),0,ROW($1:$1000)))))

    It first checks if the first character is a number and if so, returns the content of the cell starting from the first space. If the first character is NOT a number, it returns everything up to the LAST space in the cell.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post
    Perfect!!!! Thank you!
    Leesha

Posting Permissions

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