Selecting street name from address field

[kim]

I'm trying to select just the street names from an address field that
contains the street numbers and names in the same field. "12347 Hickory
Drive" or 564 "Meadows Lane". So I want to only extract the "Hickory Drive"
and "Meadows Lane". I want to separate the numbers from the names. Any help
will be very much appreciated. Thanks!

 

[John Nurick]

Based on your examples, the simplest way is to use InStr() to find the
first space, and Mid() to return the remainder of the string, e.g.

Mid([Address], InStr([Address], " ") + 1)

 

[Ed Warren]

Do you have to handle cases like:

Apt 123 12347 Hickory Drive

Ed Warren.

 

[kim]

Didn't even think of that example, but, no not yet. But... what if I do in
the future?
What would I do?
Thanks!

 

[kim]

Thank You Very Much!

Based on your examples, the simplest way is to use InStr() to find the
first space, and Mid() to return the remainder of the string, e.g.

Mid([Address], InStr([Address], " ") + 1)

I'm trying to select just the street names from an address field that
contains the street numbers and names in the same field. "12347 Hickory
Drive" or 564 "Meadows Lane". So I want to only extract the "Hickory Drive"
and "Meadows Lane". I want to separate the numbers from the names. Any help
will be very much appreciated. Thanks!

 

[Ed Warren]

1. To follow John's example, first make sure your data meets the assumed
requirements e.g. (##### CCCCCCC). That is the street follows the first
space in the field.
I have worked with a number of clients with addresses and address builders
are not database builders.

Some Quick examples are
123 44th street East
123 42nd street North
Suite A 123 first street
Suite A 123 1st street

2. If you are really lucky, your data will match the assumed pattern, if
not you will have to build a more detailed set of rules to pull out the
parts you want. Then if you can, put the street number in a field and the
street in a different field. Another generic way to do this is use the
following structure for an address
AddressLine1:
AddressLine2:
AddressLine3:
City
StateProvidenceRegion
PostCode
Country

Take a close look at how outlook handles this as an example.

Ed Warren.

 

[TedMi]

It is virtually impossible to automate this in a way which will work in all
circumstances. The reason is the the concept of "street name" is a semantic
concept, one that has meaning quite apart from its structure. Databases, and
computers in general, are very good at parsing structures, but very poor at
divining semantics. There are many, many ways of structuring an address that
is instantly understandable to humans but a mystery to machines (that's a
major reason the Post Office instituted ZIP codes). Note that the final
delivery of mail is done by a human letter carrier who eyeballs the address.

 

[John Nurick]

When things get this complicated I use regular expressions to do the
parsing. Even though they're difficult I find them less trouble than
writing huge amounts of painstaking code using VB's native string
functions.

For example (here's one I made earlier!), the regular expression below
will parse most addresses structured like these:

15 Broadway
12 Avenue Rd
660 North Avenue Suite 11
12345 North 35th St., Apt #99

into
Street Number if any
Street prefix (North|South|East|West) if any
Street name
Street type (e.g. Rd, Avenue) if any
Unit number if any

Here's the regex, or rather VBA to assemble it:

'Assemble regular expression to parse address
'Item 0: Optional street number at start of string
Regex = "^\s*(\d+\s+)?"
'Item 1: Optional North/South etc.
Regex = Regex & "(North|South|East|West)?"
'Item 2: Street Name (required)
Regex = Regex & "\s*(?w+.*?)"
'Item 3: Optional Street Type (add more types to list if required)
Regex = Regex & "(?:\s+(Street|St\.?|Lane|Ln\.?|Boulevard|Bvd\.?|" _
& "Road|Rd\.?|Drive|Dve?\.?|Avenue|Ave?\.?|Way|Crescent|Cr\.?|" _
& "Terrace|Tce\.?|Alley|Place|Pl\.?|Square|Sq\.?))?)"
'Could add an element here to look for DC-style compass quadrants
'Item 4: Optional apartment number at end of string
Regex = Regex & ",?\s*?\w*?(?:\s+#?\s*(\d+))?\s*$"

One simple way to use regular expressions to parse data in Access is
with the rgxExtract() function at
http://www.j.nurick.dial.pipex.com/Code/vbRegex/rgxExtract.htm

 

[kim]

The problem "12345 Hickory Drive"
Mid([Address], InStr([Address], " ") + 1)
worked wonderfully to select "Hickory Drive". Now I am having another
problem.
"1234 Hickory Drive #5" How can I stop at the # ?
Thanks

 

[Tom Lake]

The problem "12345 Hickory Drive"
Mid([Address], InStr([Address], " ") + 1)
worked wonderfully to select "Hickory Drive". Now I am having another
problem.
"1234 Hickory Drive #5" How can I stop at the # ?
Thanks

ModifiedAddress = ""
P = Instr([Address], "#")
If P = 0 Then
ModifiedAddress = InStr([Address], " ") + 1)
Else
ModifiedAddress = Mid([Address], InStr([Address], " ") + 1, P - 2)
End If

Tom Lake

 

[Ed Warren]

Note my previous post. It would appear your data is not all in the format
you described. As soon as you fix the #5 issue will you have a new set of
problems due to some different format.

e.g. 12345 Hickory Drive No. 5 , if so Tom's suggested parsing will produce
Hickory Drive No. 5.

Again, I strongly advise taking a good look at the field you are trying to
parse and make sure it is in the expected format. If not you will have to
handle all the variations of the data in the field. See John Nurick's post
above.

Ed Warren.

The problem "12345 Hickory Drive"
Mid([Address], InStr([Address], " ") + 1)
worked wonderfully to select "Hickory Drive". Now I am having another
problem.
"1234 Hickory Drive #5" How can I stop at the # ?
Thanks

It is virtually impossible to automate this in a way which will work in
all
circumstances. The reason is the the concept of "street name" is a
semantic
concept, one that has meaning quite apart from its structure. Databases,
and
computers in general, are very good at parsing structures, but very poor
at
divining semantics. There are many, many ways of structuring an address
that
is instantly understandable to humans but a mystery to machines (that's a
major reason the Post Office instituted ZIP codes). Note that the final
delivery of mail is done by a human letter carrier who eyeballs the
address.