separating street and housenum in address field 1

[TeddyvN]

I have got a table where in one field I have complete addresses: streetname and housenumber.
How can I separate them both into two fields?

I thought of finding the position of the space between the street and the number (" #") and then doing something with the position given... But what and how?

Any ideas? This is for a vast number of addresses, else I would do it by hand...

NB: The housenumber must also remain a text value, as some addresses have housenumbers like "3-B", and so on.



Teddy

 

[SkipVought]

Teddy,

Please post a variely of examples.

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[TeddyvN]

Dear Skip,

Some examples of address values:

Adm.gr. 15
Admiralengracht 15
Admiralengracht 15-2
Admiralengr. 15-II
Admiralengr. 15-B
1e Helmersstraat 14-III

Hope you can find a solution...


Teddy

 

[PHV]

Something like this ?
streetname: Left([fldAddress], InStrRev([fldAddress], " ") - 1)
housenumber: Mid([fldAddress], InStrRev([fldAddress], " ") + 1)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lespaul]

Note - PHV's solution will not work if the house number has a space, ie

1e Helmersstraat 14 III



Leslie

 

[TeddyvN]

Is there perhaps a way to use the InStrRev function to catch a combination of a space + a number, using a wildcard for a number? This way it would work...

E.g.: 1e Helmersstraat 14

Teddy