#### Harvey

##### Well-known Member
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.

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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

B2:

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

C2:

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

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)

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

Harvey said:
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?

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

Harvey said:
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"!

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

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?

Replies
5
Views
604
Replies
1
Views
363
Replies
6
Views
361
Replies
0
Views
103
Replies
3
Views
1K

1,202,976
Messages
6,052,871
Members
444,606
Latest member
rwmhr

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back