split address in street and nr (+additions)

[Harvey]

Hi,

I want to split an address into a street name and housenumber.
A street name can be anything as long as its text (ie. Beautiful sunflower street or anything with multiple names) and housenumber can bean integer with any extension (like 193a or 13b with green fence)
So, obviously I have to split it at the position of the number.

I know it is easy to do this with formula, but I don't know the right one.
I was thinking to use substitute or find, but can't find an example of returning the position of the first integer in a string.
Any help will be very welcome.

 

[macleanb]

Sounds easy - but its NOT!

Basic chopping is achieved with a mixture of left/right/mid and len/find. However the devils in the data as Addresses have lots of rinkydinks that will drive you nuts! There was a thread not that long ago re this

 

[Aladin Akyurek]

B2:

=TRIM(SUBSTITUTE(A2,C2,""))

C2:

=RIGHT(A2,LEN(A2)-FIND(CHAR(127),SUBSTITUTE(A2," ",CHAR(127),LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

 

[fairwinds]

Hi,

If you want to handle a house number like "13b with green fence"
in your example I think you need to substitute Aladin's second formula with:


=RIGHT(A2,LEN(A2)-MIN(FIND(0,SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9},0)&0))+1)

 

[Harvey]

Thanks a lot guys, it all works now. the array thing {1,2,3,4 ..} was exactly what I needed.
Somehow formulas don't work when I try to put arrays in them myself

 

[Aladin Akyurek]

Thanks a lot guys, it all works now. the array thing {1,2,3,4 ..} was exactly what I needed.
Somehow formulas don't work when I try to put arrays in them myself

****. 13b with green fence? I should visit Rouveen some day. Just curious: Can you give a real Dutch address like that?

 

[Harvey]

well, there arent many of them that look like that, but in my program I have to take in account house names like 20a, 20b. (thats the more regular naming)
But in some places they have names like 14a rood. (rood means red) so they contain spaces.

By the way, you should really visit Rouveen if you come to Holland XD. It is one of the most traditional villages (ie. the people walk in wooden shoes and have traditional clothing) many tourists come there to "gaze" at the people here

 

[Aladin Akyurek]

well, there arent many of them that look like that, but in my program I have to take in account house names like 20a, 20b. (thats the more regular naming)
But in some places they have names like 14a rood. (rood means red) so they contain spaces.

By the way, you should really visit Rouveen if you come to Holland XD. It is one of the most traditional villages (ie. the people walk in wooden shoes and have traditional clothing) many tourists come there to "gaze" at the people here

I live in Holland. That's why I didn't pay attention to "with green fence", totally taken by "wrong knowledge" that I've newer seen such an "huisnummer"!

 

[Harvey]

hehe, they apply to be there. I had never heard of them as well. I didn't knew you live in Holland too. I thought "The Hague" was just a joke of some kind

't schijnt dat ze in Brabant en Limburg nog wel eens zulke huisnummers hebben, met vage namen ipv a b c. Ik moet het iig in m'n programma verwerken

 

[Harvey]

Please forgive me, but I still have one question. I am trying to paste this formula in my VBA-code in order to get the position of the housenumber. When I use the following code:

housenr = WorksheetFunction.Min ("FIND(0,SUBSTITUTE(A1,{1,2,3,4,5,6,7,8,9},0)&0)")

the compiler gives an error 1004 "unknown error".

Is there anything I looked over?