# extract a number from a string

hi Folks
Got a query - how do I extract a house number from an address (which may or may not be prefixed with Apt or no.. I was thinking of some combination of ISNumber/If. Essentially it's a formula to look at a string and extract the first set of numbers it finds from it...any suggestions?
###### Who is Participating?

IT Services ConsultantCommented:
Hi,

With an address in cell [A1], place this formula in any other cell:

=TEXT(SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW(\$1:\$20),1))*ROW(\$1:\$20),0),ROW(\$1:\$20))+1,1)*10^ROW(\$1:\$20)/10),"0")

This will extact the numeric characters from the first twenty characters of the value in [A1].  Increase the "20" within the "\$1:\$20" range to increase this fixed position, if required.

I have attached a sample workbook with each of the examples of addresses that HarryHYLee mentioned above, plus two more just as further examples.

BFN,

fp.
Q-28243334.xls
0

Commented:
agwalsh,

This will be extremely difficult task if there is not standard format on the Address line.

I've see people entering their addresses in many different format, such as

33-4877 Apple Street (33 is unit number, and 4877 is street number)
Suite 33 - 4877 Apple Street  (33 is unit number, and 4877 is street number)
4699-69a Street (4699 is street number and 69a is actually street name)
4699 69a Street (4699 is street number and 69a is actually street name)
Even crazier,
101-4699-69a Street (101 is unit number, 4699 is street number and 69a is actually street name)

Without some sort of standardize data, it's very hard to do this with formula or VBA macro.
0

Commented:
This formula does specifically what you ask - it extracts the "first set of numbers" (up to 5 digits) from an address in A1

=LOOKUP(10^5,MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&1234567890)),{1,2,3,4,5})+0)

regards, barry
0

IT Services ConsultantCommented:
33-4877 Apple Street
Suite 33 - 4877 Apple Street
4699-69a Street
4699 69a Street
101-4699-69a Street

barry: Your formula returns the following for the examples above:

33
33
4699
4699
101

The formula I posted returns this set:

334877
334877
469969
469969
101469969

Given the examples were not posted in the question, it is difficult to say what constitutes "the first set" in any given text value.

Perhaps agwalsh will be kind enough to confirm/clarify &/or provide a set of test data (including expected results).
0

Author Commented:
hi Folks
Thank you very much for your answers to date...sigh, I suspected it would be trickier than I thought...attached please find file as per fanpages request illustrating sample data and desired result..
0

IT Services ConsultantCommented:
Thanks for the file/data.

Both barry's & my suggestions produce values to match your desired results.
0

Commented:
Agreed fp,

You can apply my suggestion by using my suggested formula with cell references changed to match your data, i.e. in C3 copied down

=LOOKUP(10^5,MID(B3,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B3&1234567890)),{1,2,3,4,5})+0)

If the address field might be blank or not contain a number then you can add an IFERROR function with appropriate text, e.g.

=IFERROR(LOOKUP(10^5,MID(B3,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B3&1234567890)),{1,2,3,4,5})+0),"No number")

regards, barry
0

Author Commented:
Thank you both - very elegant solutions...much appreciated. I've chosen the one from fanpages as the best because it doesn't use array functions - because the person I'm helping  will definitely forget the whole key combination for entering arrays... :-)
0

Commented:
Thanks for the points agwalsh.

Note: my suggestion doesn't need to be "array entered"......

regards, barry
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.