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

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

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. =if(isnumber(mid(a8;4;4)+0);mid(a8;4;4)+0;0)

3. If you are using Excel 2007 or later, then try:

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

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

#### Posting Permissions

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