Separate Out House Number From Street Address

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello:

I have a database where someone included the house number in the same field with the street name.

Is there a way I can put the house number in a separate column?

Any help will be greatly appreciated!

Sandy
 
Hi Sandy.

Use an Update query to break field into two and populate the new HouseNumber
column.

Before you do this, though, how do do you decide where the house number
ends? Is it the first space? What about entries such as:
- 74B Field St
- Unit 4, 32 Somme St
- Lot 64 ...
- Care/Of Someone Else ...
It really is notoriously difficult to handle addresses of all forms,
especially if your database includes addresses in multiple countries. That's
why many of us leave the number and street in the same field, even though a
good argument can be made for separating the number and street type into
different fields.

Assuming you treat the first space as the end of the number part (so 74B is
the "number" in the first example above), try this:

1. Create a query into this table.

2. Change it to an Update query: Update on Query menu.
Access adds an Update row to the grid.

3. In the Update row under your HouseNumber column, enter:
Left([Address], Instr([Address], " ")-1)

4. Run the Update query to populate the number.

5. In the Update row under the Address field, enter:
Mid([Address], Instr([Address], " ")+1)

6. Run the Update query to chop the number off the beginning of the adress.

7. Manually correct the entries where this process got it wrong.
 
I'd suggest just update a street name field and leave the address as is.
If you have a lot of these it would also be worth while to run then through
a program that will normalize (in post office terms ) the address.
They tend to be expensive but most places that do mass mailings can probably
do it for you for a small fee.

Unless you don't value your time a mailing service is the best way to handle
such lists in most cases.




Allen Browne said:
Hi Sandy.

Use an Update query to break field into two and populate the new HouseNumber
column.

Before you do this, though, how do do you decide where the house number
ends? Is it the first space? What about entries such as:
- 74B Field St
- Unit 4, 32 Somme St
- Lot 64 ...
- Care/Of Someone Else ...
It really is notoriously difficult to handle addresses of all forms,
especially if your database includes addresses in multiple countries. That's
why many of us leave the number and street in the same field, even though a
good argument can be made for separating the number and street type into
different fields.

Assuming you treat the first space as the end of the number part (so 74B is
the "number" in the first example above), try this:

1. Create a query into this table.

2. Change it to an Update query: Update on Query menu.
Access adds an Update row to the grid.

3. In the Update row under your HouseNumber column, enter:
Left([Address], Instr([Address], " ")-1)

4. Run the Update query to populate the number.

5. In the Update row under the Address field, enter:
Mid([Address], Instr([Address], " ")+1)

6. Run the Update query to chop the number off the beginning of the adress.

7. Manually correct the entries where this process got it wrong.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
Sandy said:
Hello:

I have a database where someone included the house number in the same field with the street name.

Is there a way I can put the house number in a separate column?

Any help will be greatly appreciated!

Sandy
Click to expand...
Click to expand...
 
Thanks so much, Allen. I'll give it a try

Sand

----- Allen Browne wrote: ----

Hi Sandy

Use an Update query to break field into two and populate the new HouseNumbe
column

Before you do this, though, how do do you decide where the house numbe
ends? Is it the first space? What about entries such as
- 74B Field S
- Unit 4, 32 Somme S
- Lot 64 ..
- Care/Of Someone Else ..
It really is notoriously difficult to handle addresses of all forms
especially if your database includes addresses in multiple countries. That'
why many of us leave the number and street in the same field, even though
good argument can be made for separating the number and street type int
different fields

Assuming you treat the first space as the end of the number part (so 74B i
the "number" in the first example above), try this

1. Create a query into this table

2. Change it to an Update query: Update on Query menu
Access adds an Update row to the grid

3. In the Update row under your HouseNumber column, enter
Left([Address], Instr([Address], " ")-1

4. Run the Update query to populate the number

5. In the Update row under the Address field, enter
Mid([Address], Instr([Address], " ")+1

6. Run the Update query to chop the number off the beginning of the adress

7. Manually correct the entries where this process got it wrong
 
Back
Top