Extracting Street Number

[Raphi]

Hi,

I'm trying to clean up a large database in Access. I have one field for
address, which needs to be broken up into Street Number, Street Name, and
Street Label (St., Road, etc.)

The problem is that the data is very dirty. So some addresses will be
standard "456 XYZ Road," while others won't have a number and will just say
"XYZ Industrial Park," meaning I can't just use Instr to search for the
first space because sometimes the street number needs to be left blank.
Rather, I need to use a command to cycle the first few characters and tell
if they're numeric or alphabetical, copying them only if they're numeric.
Anyone have any ideas how to do this (i.e. how to tell if they're numeric or
not) or any link that has a little code snippet to do this?

Thanks in advance.

[Raphi]

Upon closer examination of my data, it appears that there's good news and
bad news. The bad news is that often the street number comes after the
street name, e.g. XYZ Road 3, so I can't just use Val to pick out the first
numerical values from the string, because it will run into the alphabetical
characters first and return a -1.
On the other hand, there are no streets with numbers in them. So I just need
to take all of the numbers in the string (though some may have characters in
the middle, e.g. 13/7, and I want that slash to remain with the numbers) and
store them separately. Any idea how I can do that? How can I pull out just
the part of the string from the first number to the last number?
Thanks again in advance.
"Raphi" <le**@DELETE.TH IS.optonline.ne t> wrote in message
news:ce******** ***@netnews.upe nn.edu...

Hi,

I'm trying to clean up a large database in Access. I have one field for
address, which needs to be broken up into Street Number, Street Name, and
Street Label (St., Road, etc.)

The problem is that the data is very dirty. So some addresses will be
standard "456 XYZ Road," while others won't have a number and will just say "XYZ Industrial Park," meaning I can't just use Instr to search for the
first space because sometimes the street number needs to be left blank.
Rather, I need to use a command to cycle the first few characters and tell
if they're numeric or alphabetical, copying them only if they're numeric.
Anyone have any ideas how to do this (i.e. how to tell if they're numeric or not) or any link that has a little code snippet to do this?

Thanks in advance.

[Dave Stone]

If the 'number substring' contains no embedded spaces, you could scan for
the first numeric char., and the first subsequent space, and take the chars
between as being the street number. I don't know how you can differentiate
the street name and label though. You might have 'Acacia Avenue' or 'The
Poplars'..... And how about named (rather than numbered) addresses?

Dave

"Raphi" <le**@DELETE.TH IS.optonline.ne t> wrote in message
news:ce******** ***@netnews.upe nn.edu...

Upon closer examination of my data, it appears that there's good news and
bad news. The bad news is that often the street number comes after the
street name, e.g. XYZ Road 3, so I can't just use Val to pick out the first numerical values from the string, because it will run into the alphabetical characters first and return a -1.
On the other hand, there are no streets with numbers in them. So I just need to take all of the numbers in the string (though some may have characters in the middle, e.g. 13/7, and I want that slash to remain with the numbers) and store them separately. Any idea how I can do that? How can I pull out just
the part of the string from the first number to the last number?
Thanks again in advance.
"Raphi" <le**@DELETE.TH IS.optonline.ne t> wrote in message
news:ce******** ***@netnews.upe nn.edu...

Hi,

I'm trying to clean up a large database in Access. I have one field for
address, which needs to be broken up into Street Number, Street Name, and Street Label (St., Road, etc.)

The problem is that the data is very dirty. So some addresses will be
standard "456 XYZ Road," while others won't have a number and will just say

"XYZ Industrial Park," meaning I can't just use Instr to search for the
first space because sometimes the street number needs to be left blank.
Rather, I need to use a command to cycle the first few characters and tell if they're numeric or alphabetical, copying them only if they're numeric. Anyone have any ideas how to do this (i.e. how to tell if they're

numeric or

not) or any link that has a little code snippet to do this?

Thanks in advance.

[Bob Quintal]

"Raphi" <le**@DELETE.TH IS.optonline.ne t> wrote in
news:ce******** ***@netnews.upe nn.edu:

Hi,

I'm trying to clean up a large database in Access. I have one
field for address, which needs to be broken up into Street
Number, Street Name, and Street Label (St., Road, etc.)

The problem is that the data is very dirty. So some addresses
will be standard "456 XYZ Road," while others won't have a
number and will just say "XYZ Industrial Park," meaning I
can't just use Instr to search for the first space because
sometimes the street number needs to be left blank. Rather, I
need to use a command to cycle the first few characters and
tell if they're numeric or alphabetical, copying them only if
they're numeric. Anyone have any ideas how to do this (i.e.
how to tell if they're numeric or not) or any link that has a
little code snippet to do this?

Thanks in advance.

I can't think of a single valid reason to break up an address field
into street number, street name and street type.

Save yourself a lot of trouble for nothing.

--
Bob Quintal

PA is y I've altered my email address.

[Bernard Peek]

In message <ce***********@ netnews.upenn.e du>, Raphi
<le**@DELETE.TH IS.optonline.ne t> writes

Hi,

I'm trying to clean up a large database in Access. I have one field for
address, which needs to be broken up into Street Number, Street Name, and
Street Label (St., Road, etc.)

The problem is that the data is very dirty. So some addresses will be
standard "456 XYZ Road," while others won't have a number and will just say
"XYZ Industrial Park," meaning I can't just use Instr to search for the
first space because sometimes the street number needs to be left blank.
Rather, I need to use a command to cycle the first few characters and tell
if they're numeric or alphabetical, copying them only if they're numeric.
Anyone have any ideas how to do this (i.e. how to tell if they're numeric or
not) or any link that has a little code snippet to do this?

If you need to do this I would consider multi-pass processing with a
flag field to indicate that the address has been processed. Use a
different value in the flag field for each rule that you apply, that way
you can easily reverse out any errors you discover later.

Look for a series of rules that will each process one format of address,
and then set the flag. The obvious one is where the first token in the
address is a numeric group, extract this and set the flag.

Keep a count of the number of unprocessed records and when this reaches
a manageable level switch to manual processing.

Randomly select a number of the automatically processed addresses for
manual checking.

If you intend to mailshot the people on the list make sure that you ask
for address corrections and return of undeliverable mail, at least for
the first mailshot after you use the new addresses. Expect a higher than
normal number of returns.

--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.

[Fred Zuckerman]

I will break the street name from the street number if I plan to sort by
street names, or look for addresses close by.
Fred Zuckerman
"Bob Quintal" <rq******@sPAmp atico.ca> wrote in message
news:Xn******** **************@ 66.150.105.49.. .

I can't think of a single valid reason to break up an address field
into street number, street name and street type.

Save yourself a lot of trouble for nothing.

[Bob Quintal]

"Fred Zuckerman" <Zu********@sbc global.net> wrote in
news:WG******** *********@newss vr27.news.prodi gy.com:

I will break the street name from the street number if I plan
to sort by street names, or look for addresses close by.
Fred Zuckerman
"Bob Quintal" <rq******@sPAmp atico.ca> wrote in message
news:Xn******** **************@ 66.150.105.49.. .

I can't think of a single valid reason to break up an address
field into street number, street name and street type.

Save yourself a lot of trouble for nothing.

Much better to just use the postal code for that.

--
Bob Quintal

PA is y I've altered my email address.

[Fred Zuckerman]

Unless you only want 'close' neighbors (about a block)
Postal code would include 1000's of addresses.
Fred

"Bob Quintal" <rq******@sPAmp atico.ca> wrote in message
news:Xn******** **************@ 66.150.105.49.. .

"Fred Zuckerman" <Zu********@sbc global.net> wrote in
news:WG******** *********@newss vr27.news.prodi gy.com:

I will break the street name from the street number if I plan
to sort by street names, or look for addresses close by.
Fred Zuckerman
"Bob Quintal" <rq******@sPAmp atico.ca> wrote in message
news:Xn******** **************@ 66.150.105.49.. .

I can't think of a single valid reason to break up an address
field into street number, street name and street type.
Save yourself a lot of trouble for nothing.

Much better to just use the postal code for that.
Bob Quintal