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?
LVL 1
agwalshAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Harry LeeCommented:
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
[ fanpages ]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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
barry houdiniCommented:
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

[ fanpages ]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
agwalshAuthor 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..
EE-extracting-number-from-addres.xlsm
0
[ fanpages ]IT Services ConsultantCommented:
Thanks for the file/data.

Both barry's & my suggestions produce values to match your desired results.
0
barry houdiniCommented:
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
agwalshAuthor 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
barry houdiniCommented:
Thanks for the points agwalsh.

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

regards, barry
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.