MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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!


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

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

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

I'm impressed

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!

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

Re: I'm impressed

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

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

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.

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

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

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

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

It helped!

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

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

That's right!


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