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

allredkj

New member
Joined
Sep 14, 2012
Messages
4
Reaction score
0
Points
0
Location
Fort Worth, TX
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!!!
 

CheshireCat

New member
Joined
Dec 30, 2011
Messages
121
Reaction score
0
Points
0
Location
Victoria, Canada
Excel Version(s)
Microsoft Excel 2013
If you are using Excel 2007 or later, then try:

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

Zack Barresse

Super Moderator
Staff member
Joined
Mar 16, 2011
Messages
112
Reaction score
0
Points
0
Location
Oregon, United States
Excel Version(s)
365, Online, iOS, iOS for iPad
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.
 
Top