How to separate one field into two? (2000)

Topic

New Reply

We have a field that contains an address in the form of house number and street. Is there a way to write a query that will separate the house number (which always comes before a space and then the street name) from the street name and put them into separate fields? The house numbers may be anywhere from one to four characters. The street names obviously are irregular as well, and sometimes contain multiple words. The two fields I want the data in already exist in the table. I’m hoping there is some kind of update query that will separate the data (otherwise it’s cut-and-paste for 4329 records!)

Thanks,
-cynthia

Reply | Quote

Replies

If you can guarantee that the street number (1 to 4 characters) is the first part of the field and what follows the space is in fact the street name then you should be able to use the InStr function, eg.
Suppose that the field name is currently Address, then the following should suffice:
Left(Address, Instr(1, Address, ” “, 2)-1) as StreetNumber, Mid(Address, Instr(1, Address, ” “, 2)+1) as Streetname

Reply | Quote

Here is a slight variation of Patt’s suggestion.

It also uses the Instr function, but uses the right function instead of the Mid function.

the street number can be of any length, but must not contain a space.
I just noticed that the closing ) got left out of my picture!

Reply | Quote

Thanks to both of you! I will try this tomorrow.
I had an idea that a query could perhaps do the trick – but beyond that I was pretty well baffled.
Thank you,
-cynthia

Reply | Quote

Thanks to both of you! I will try this tomorrow.
I had an idea that a query could perhaps do the trick – but beyond that I was pretty well baffled.
Thank you,
-cynthia

Reply | Quote

Here is a slight variation of Patt’s suggestion.

It also uses the Instr function, but uses the right function instead of the Mid function.

the street number can be of any length, but must not contain a space.
I just noticed that the closing ) got left out of my picture!

Reply | Quote