How to extract the number from a street address?

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

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

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

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

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

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.

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

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

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!

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!

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