Archive of Mr Excel Message Board


Back to Data in Excel archive index
Back to archive home

How to extract the number from a street address?

Posted by Johan van Arragon on April 26, 2001 7:24 AM
I have a spreadsheet that contains a column with street addresses, including numbers. I need to extract only the numbers from that column and put them in another column. How do I do that?
To complicate things, the adress strings can look like this:
PALACEPLEIN 7
but also like this:
DE GRENT 16 -18
or this:
A V ROYENSTRAAT 106 A

Thanks for any help!


Re: How to extract the number from a street address?

Posted by Kevin James on April 26, 2001 7:33 AM
Well, my first choice would be to go running frantically into the streets, pulling my hair out. But since that won't fix the problem....

Try parsing the field using FIND, within combinations of LEFT, MID and RIGHT string manipulations.

Please excuse my attempt at humor


Re: How to extract the number from a street address?

Posted by Jerid on April 26, 2001 10:00 AM

Hi Johan

If this is a one time thing it might help if you use (Data Menu, Text to Columns option) to separate out the address using the spaces as delimiters, then clean things up from there.

Jerid


I'm impressed

Posted by Kevin James on April 26, 2001 12:15 PM
Hi Jerid:

For the years that I've been using Excel, I have never used that option. You just taught me something I never paid any attention to. Thanks!


Re: I'm impressed

Posted by Jerid on April 26, 2001 12:30 PM

No Problem, I just learned about it the other day myself, and thought is was worth passing on.


Re: How to extract the number from a street address?

Posted by Johan van Arragon on April 26, 2001 1:33 PM


Thank you, Jerid. You are right, your solution helps.
Nevertheless, I had hoped there is a way to single out the numeric parts of a string and copy those parts to another cell. Or more precise: to select the righthand part of the address, starting with the first digit. That would enable a one-step solution to the problem. Because, as you can see by the address examples
(PALACEPLEIN 7
DE GRENT 16 -18
A V ROYENSTRAAT 106 A),
the street names can contain a varying number of spaces, and so can the numbers. The Text to Columns option would therefore yield a considerable number of columns, with the street numbers in different columns.
I keep looking for the ideal one-step solution, but until then I am glad with your suggestion.

Kind regards,
Johan van Arragon


Re: How to extract the number from a street address?

Posted by Johan van Arragon on April 26, 2001 1:47 PM

Maybe this before the deselect section.

dim iCurLocation as integer
iCurLocation = ListBox1.ListIndex

'Deselect Section goes here

'Change the reset section to this
ListBox1.ListIndex = iCurLocation

Have fun.


Re: How to extract the number from a street address?

Posted by Jerid on April 26, 2001 2:15 PM

The entire address is a string to Excel/Your PC, not a string with a number at the end.

The difficult part is that your addresses don't follow some kind of pattern, or you could just use Left(), Mid() or Right() functions to pick it out.

You could insert a column next to the address column and add the following formula,[ =Right("A1",5) ] then auto fill the formula down, and use (Data Menu, Text To Columns) on the new column.


Jerid


Re: How to extract the number from a street address?

Posted by Stephane Parent on April 26, 2001 2:27 PM

Hi Johan,

I like Jerid solution, but mine should work without any manual intervention.
It use array formula and may be slow, but it should do the job.
try putting this formula in B1. A1 contains your complete street address. Addresses should not be longer than 100 caracters (or change the 100 to a bigger number)
Since, it's an array formula, you must hold CTLR and Shift while pressing enter after entering it.
=RIGHT(A1,LEN(A1)-MATCH(FALSE,ISERROR(1*MID(A1,ROW(1:100),1)),0)+1)

I hope it helps!

Stephane Parent


Re: How to extract the number from a street address?

Posted by Jerid on April 27, 2001 8:12 AM

I like that, it took me a little while to figure out how it works but I finally got it.

If you change the formula to =RIGHT(A1,LEN(A1)-MATCH(FALSE,ISERROR(1*MID($A$1,ROW($1:$100),1)),0)+1) (add the $ for the Row references) and do the control, shift, enter in cell A1 then you can auto fill the formula to the end.

Jerid


It helped!

Posted by Johan van Arragon on April 28, 2001 3:58 PM
Hello Stephane,

It helped! It did exactly what I wanted it to do, after I changed the commas to semi-colons Dutch version).
Thank you very much for your help.

Regards,
Johan van Arragon =RIGHT(A1,LEN(A1)-MATCH(FALSE,ISERROR(1*MID(A1,ROW(1:100),1)),0)+1) I hope it helps! Stephane Parent


That's right!

Posted by Johan van Arragon on April 28, 2001 4:08 PM


That is exactly my experience. I forgot to mention the &s in my earlier mail. It took me a while too to understand how it was supposed to work and why it didn't work, but when I changed the commas to semi-colons and added the $s to the row references, it worked fine and it wasn't even slow.
Thank you again,

Johan van Arragon


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.