seperate numbers from text in an address column

[Guest]

Greetings,
I have a database that has addresses in a column like.. 123 Main Street

How can I split the columns so I have the numbers in a seperate column from
the street name ie.. column(123) column (Main Street)

Thanks,

Rick

 

[Brian]

if it can be assumed that there is always a space between the numbers
and the text portion of the address, then you could write a query which
would separate the numbers and letters.

If there is a row in your table for "numbers" then you can create an
update query and set the "numbers" field to Update To:
"=Left([address],InStr(1,[address]," ")-1)" This is assuming that the
field containing the entire address is named [address]

This should get you started, let me know if you need further
instruction.

Bp

 

[Guest]

Hello Brian, thanks for the reply. Pleasee bare with me, it has been a very
long time since I used Access.

In query mode, how would I enter the information you gave me? Field, sort,
criteria etc.. If you don't mind treat me like an absolute beginner.
There is a column for "Address" but none for "numbers". Do I have to create
a column for numbers first?

if it can be assumed that there is always a space between the numbers
and the text portion of the address, then you could write a query which
would separate the numbers and letters.

If there is a row in your table for "numbers" then you can create an
update query and set the "numbers" field to Update To:
"=Left([address],InStr(1,[address]," ")-1)" This is assuming that the
field containing the entire address is named [address]

This should get you started, let me know if you need further
instruction.

Bp

Greetings,
I have a database that has addresses in a column like.. 123 Main Street

How can I split the columns so I have the numbers in a seperate column from
the street name ie.. column(123) column (Main Street)

Thanks,

Rick

 

[Brian]

No problem.

The first step is to open the table in design mode and add another
field named "numbers" or whatever you want. Best to leave it as a text
field.

Next you want to design a new criteria. Add the table that has the
full address as well as your new numbers field.
Add the new numbers field to the bottom area of the query (either drag
it down, or select from the combo box.

From the Top menu Choose Query > Update Query. This will change from a

select query to an update query

Now down at the bottom part of the query box you should have a row that
says "Update To" This is where you enter the formula that I listed
earlier.

Last you can View the query be clicking on the icon right below file,
looks like an excel sheet. It will ask you if you want to update some
rows and you choose yes.

Now that i'm reading your question, you probably also need to make an
update query to strip the numbers off the address field after you have
moved them over.
This would be another update query, except this time the only field
would be the address field, and the formula would probably look like:

Mid([address],InStr(1,[address]," "),Len([address]))

Hope this helps. Be sure to rate this post

Hello Brian, thanks for the reply. Pleasee bare with me, it has been a very
long time since I used Access.

In query mode, how would I enter the information you gave me? Field, sort,
criteria etc.. If you don't mind treat me like an absolute beginner.
There is a column for "Address" but none for "numbers". Do I have to create
a column for numbers first?

if it can be assumed that there is always a space between the numbers
and the text portion of the address, then you could write a query which
would separate the numbers and letters.

If there is a row in your table for "numbers" then you can create an
update query and set the "numbers" field to Update To:
"=Left([address],InStr(1,[address]," ")-1)" This is assuming that the
field containing the entire address is named [address]

This should get you started, let me know if you need further
instruction.