Delete numbers in text field

[TomC]

I have a street address text field. I would like to remove the address
number from the beginning of the field and be left with just the street name
so that I can sort the file on street name. I am aquainted with Access but
do not know Visual Basic. Is there something available that I can use to
acomplish this?? Thanks in advance for any help. Tomc

 

[BruceM]

You could use an expression like this as a calculated field in query design
view:

StreetName: Mid([AddressField],Instr([AddressField]," ") + 1)

This should return everything to the right of the first space in the address
field. Sort on the calculated field, then the address field. Be advised it
may not always do the trick, depending on the data in the field. For an
address without a number, for instance, it will eliminate the first word in
the address.

A better option in the long run would be to add a StreetNumber field and a
StreetName field to the table, then use an update query to extract the
street number as the value for the StreetNumber, maybe with an expression
something like this:

Left([AddressField],Instr([AddressField]," ") - 1)

You could use an expression like the first one to populate the StreetName
field.

You will want to review all of the addresses before deleting the original
Address field.

There are other ways you could go about the same thing, but in general I
think you would do well to keep the original field rather than updating it.
It is probably the best way to check whether the new fields contain the data
they should.

 

[Jeff Boyce]

Tom

Don't bother with code.

Create a query. Add the address table. Add the street address field.

Add a new field that is something like [streetname].

Now comes the hard part ... you need to tell Access how to get to the
streetname only.

If you are exceptionally lucky, all of your street address are of the form
"#### StreetName". (hah!)

More likely, you have street addresses like:

"12345 Elm St."
"12345 1st"
"12345 Fifth Avenue"
"12345 Elm, Suite 205"

Now, how would you explain to a very patient (but very 'slow') assistant how
to parse these different versions of "street address"? Have you covered all
the possible variations?

That would be easier than explaining to Access ... (if you're still game,
use the query to parse out the street name)

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[TomC]

Thanks Bruce and Jeff for taking the time to answer my call for help. It
worked!!
Tomc

You could use an expression like this as a calculated field in query design
view:

StreetName: Mid([AddressField],Instr([AddressField]," ") + 1)

This should return everything to the right of the first space in the address
field. Sort on the calculated field, then the address field. Be advised it
may not always do the trick, depending on the data in the field. For an
address without a number, for instance, it will eliminate the first word in
the address.

A better option in the long run would be to add a StreetNumber field and a
StreetName field to the table, then use an update query to extract the
street number as the value for the StreetNumber, maybe with an expression
something like this:

Left([AddressField],Instr([AddressField]," ") - 1)

You could use an expression like the first one to populate the StreetName
field.

You will want to review all of the addresses before deleting the original
Address field.

There are other ways you could go about the same thing, but in general I
think you would do well to keep the original field rather than updating it.
It is probably the best way to check whether the new fields contain the data
they should.

I have a street address text field. I would like to remove the address
number from the beginning of the field and be left with just the street
name
so that I can sort the file on street name. I am aquainted with Access
but
do not know Visual Basic. Is there something available that I can use to
acomplish this?? Thanks in advance for any help. Tomc