Results 1 to 4 of 4

Thread: Left - remove leading zeros and return number portion of st address, else return 0

  1. #1
    Neophyte allredkj's Avatar
    Join Date
    Sep 2012
    Location
    Fort Worth, TX
    Posts
    4
    Articles
    0

    Left - remove leading zeros and return number portion of st address, else return 0



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

    I'd like to extract the street number portion only for the address string and if none exists, I'd like it to return a '0'. An example of the data is below:
    0005613Elliott Reeder Rd
    0007601Glenview Dr
    0007724Maplewood Ave
    0007348Boulevard 26
    0006120Watauga Rd # C
    (this row is null)

    I'd like to see the following after the formula is applied:
    5613
    7601
    7724
    7348
    6120
    0

    I appreciate your assistance!!!

  2. #2
    Acolyte eisayev's Avatar
    Join Date
    Oct 2012
    Location
    Baku, Azerbaijan
    Posts
    33
    Articles
    0
    =if(isnumber(mid(a8;4;4)+0);mid(a8;4;4)+0;0)

  3. #3
    Conjurer CheshireCat's Avatar
    Join Date
    Dec 2011
    Location
    Victoria, Canada
    Posts
    121
    Articles
    0
    Excel Version
    Microsoft Excel 2013
    If you are using Excel 2007 or later, then try:

    Code:
    =IFERROR(TEXT(LEFT(A2,7)*1,0),0)

  4. #4
    Super Moderator Zack Barresse's Avatar
    Join Date
    Mar 2011
    Location
    Oregon, United States
    Posts
    106
    Articles
    0
    If the number of leading zeros is not known, this will be more robust...

    Code:
    =LOOKUP(9.99E+307,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW(INDIRECT("1:"&LEN(A1))))))
    This assumes you're looking at the value in A1.
    Regards,
    Zack Barresse

Posting Permissions

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