separate street number from street name for sorting purposes in Access

proffittware used Ask the Experts™
I have street addresses in one field, which includes the number and the name.

I want to separate the number of the street address from the rest and have two fields so that sorting will be more accurate.

The numbers are separated by a space in the address.

I would like to do an update query to fix this problem.

Can anyone help ? Or have any other ideas ?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016
you can get the number portion with

 left([address],instr([address]," ")-1)

the street portion
    mid([address],instr([address]," ") +1)

in a query

select address, left([address],instr([address]," ")-1) as Num,  mid([address],instr([address]," ") +1) as street
from tableName

Expert of the Quarter 2010
Expert of the Year 2010
Create 2 more columns, one NUMBER for the street number and one TEXT for the address.

Update Tbl set
street_no = left(full_address, full_address + ' ', ' ') -1),
street_add = Mid(full_address,InStr(full_address+' ',' ')+1)
where len(full_address) > 0

This assumes all addresses have a number before the first space.
12, James St << this fails

Be careful using NUMBER data type for street No, as the number could be like 6/45 or 45-47.

How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Top Expert 2010

Hello proffittware,

I think this question may be subtly more difficult than you think it is, and the main reason why has everything
to do with JVWC's comment.

"Street numbers" simply cannot be counted on to be, um, numbers; in addition to JVWC's examples you can
also have items like "1234A SomeNameHere Street".

Thus, if you are going to extract that data element into its own column, then you will have to use a text data
type for it.

Now sorting, if you choose to sort, becomes an adventure.  Consider these two addresses:

33 Main Street
101A Main Street

If you try sorting on the "number" portion, which should come first?  In a text sort, "101A" will sort before "33".

Then when you try to sort the streets, consider these pairs:

33 Main Street N
33 N Main St

33 2nd Ave
33 Second Ave


Might I also point out::
Unit 5, 23 Main Street


Suite 5A
5th Floor
289 George Street

Top Expert 2010


Just gets better and better, doesn't it?


A can of worms, opened, turns out to be a barrel of snakes.
Given that the original bad design has allowed a complex address to be stored in a single field, applying an algorithm to restore some semblance of order is not something that will be achieved via a simple update query.
You have a single string field (I presume) to hold the address that really is at least 5 or 6 separate fields
eg: working backwards:-  
                                                postcode, country, state, City, suburb, street address2, Street Address1, [street number]
* square brackets indicate an optional choice of storing the street number separately

If you can modify the database and these changes need to persist beyond this single exercise, I would suggest a data conversion: Not a simple exercise.
Firstly,  I would suggest leaving the old address field alone (who knows what other software might access it) and add a number of new fields along the lines outlined above.
Secondly, design some queries to extract out the values for these fields from the old address. It will probably require a number of functions to do this.
Thirdly, generate a report to verify that your extracted values are correct and not garbage.
Fourth, design your search to work on these new fields.

Even if you can only extract out the suburb, city, state and postcode information and leave the rest of the street address as a single lump, you will have improved your search accuracy by an order of magnitude.

I can't help myself...

What about PO Box 88, ...
don't forget
GPO Box 88,
or even
G.P.O. Box 88


Thanks to everyone for responding. But my application was a controlled address environment.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial