Solved Parse street address

[Eljefegeneo]

I've gone brain dead for the last hour trying to figure out how to parse out the street address from a string. Tried InStr, Left, etc., but keep getting into dark places. As an example, the address might be:
1 State Street, Anywhere, NY 1001
I know how to get the city state and zip just can't figure out get out the 1 State Street. I know the answer is in front of me but I just can't see it
Thanks.

 

[pbaldy]

You could use InStr() to find the first comma along with the Left() function.

 

[Gasman]

I'd probably use the Split() function these days.

 

[Galaxiom]

Probably not what you really need here but I though I would mention one way is to parse addresses is with a Cartesian join to a list with all the streets types, (Street, Road, Crescent etc plus their abbreviations). Then use InStr() with this list to find what is before and after the street type. A space gets concatenated into the search expression.

I have a table with the StreetTypes and another field with their associated abbreviations. The fields can be unioned to get both the full type and abbreviations. The table can also be used to translate between the full type and abbreviation.

I use this technique on addresses without the suburb to get information about streets like "Some Street West" and find errors such as where the suburb has been put in the Address field.

 

[gemma-the-husky]

If you use split() (based on the comma, say) then you will get an array with separate strings for each section in the address. We can come back to splitting the state and zip code later. The trouble is if some addresses have more than 2 commas, the split will not always be consistent. and you will still have a somewhat similar issue, although possibly more tractable.

ie
street address,
town/city,
state and zipcode

It also depends how many addresses you have as to whether you can subsequently tidy them up manually.

This is where @Galaxiom was going, to try to get the street address always in the same segment within the parsed address.

so this sort of code

dim s() as string  'an array of variable dimension
dim x as long

s = split("1 State Street, Anywhere, NY 1001",",") 'split the array into segments, based on comma separators
for x = 0 to ubound(s)  'the array is zero based. if there are no commas everything goes in s(0)
      msgbox("Segment: " & x & "   " & s(x))
next

 

[The_Doc_Man]

While there are several viable approaches, this will always come back to the problem that people don't enter addresses uniformly. I have something that would address the problem with a bit of extra work.

Text Parsing Support as Class Module

I've been working on something I used a lot at work to help me more easily decipher complex text inputs. For the Navy, I used this as a part of a program to analyze IIS logs, decode complex names, and I even once used a variant of this under OpenVMS to build a command-line calculator for the...

www.access-programmers.co.uk

This MIGHT be more than you want because it is a tool - but not the final answer. You could use it in a loop to pick apart pieces but the loop would then have to decide which piece you are working on. But it WOULD split text elements apart for you and I have not seen reliability issues with it.

In the final analysis, no matter what approach you take, the non-uniformity of input address styles will be the biggest problem.

 

[jdraw]

Just a comment:

Many people have built careers by parsing names and addresses. There are many schemes, but as long as the application allows user to format input, no scheme is100%. There used to be a saying, you use a variety of schemes and pattern matches, but in the end it's "IEH - Eyeball". A manual review with human eyeballs to provide final inspection/correction.

**IEH - Eyeball is a play on words of the older IBM utilities IEHPROGM, IEFBR14, IEBGENER...

 

[Eljefegeneo]

Thanks for all the suggestions. And yes I know that there are many different variations to address strings. Some use no commas, some use other characters, but at least most use the standard formula of: Street, City, State, Zip.

So I added a unbound text field, txtAddress to which I paste the address string. Then I use the following to first separate the street address and insert it into my Address field. Then I separate the remaining string into another string and then parse it using the code found on the Microsoft help page:

https://docs.microsoft.com/en-us/of...ow-to-parse-city-state-zip-to-separate-values

So now my code is:

 If Me.Dirty Then
     Me.Dirty = False
         End If
Dim strAdd As String
strAdd = txtAddress
         'First separate the street address from the string and put it in the Address Field
Me.Address = Left([strAdd], InStr([strAdd], ",") - 1)
           'Then separate the rest of the string to be parsed
Me.txtAddress = Mid([strAdd], InStr([strAdd], ",") + 1)

Dim City1 As String, State1 As String, Zip1 As String
     ParseCSZ txtAddress, City1, State1, Zip1
         City = City1
               State = State1
               Zip4 = Zip1

I know that if I worked hard and long enough I could probably modify the ParseCSZ module to include the street address, but the above works so I am done.

 

[Pat Hartman]

Depending on where you are in the US, the compass directions can be either after the house number or after the street designation.
10421 NW 17th Ave.
552 Main St S

The street designation might be abbreviated or not and if abbreviated might end in a period or not. Best to make a table of the common possibilities

Sometimes compass designations are spelled out, but usually not. Abbreviations tend to not include punctuation.

Once you parse the names, make sure you create all the appropriate fields for data entry going forward so this becomes a one-time event. As the others have mentioned, you almost always need human eyeballs on the results of your code.

The only client I ever worked for that did more than the usual addr1, addr2, city, state, zip was Reader's Digest. Because addresses were at the heart of their business they entered them in discrete fields. I think they used nine or ten fields to hold the various parts. When you need to reduce your mailing costs, you need to sort the mail for the PO and that requires precision.

 

[Gasman]

Something to be said for purchasing some sort of zipcode DB, that has all the addresses correctly stored.?
Over here we have a PAF product which is from our Post Office, but not cheap.

Colin (isladogs) has a similar product I believe now.

 

[jdraw]

Have you reviewed say 50-100 records to see if there is any sort of consistent pattern?
Perhaps code for a fairly consistent pattern , then check for next pattern ---eat the "elephant" in small pieces.

Yes, Gasman, same thing in Canada -- a $$$ subscription to CanadaPost.

 

[Pat Hartman]

I went to the USPS site to see if they had a tool. I thought they used to but I couldn't find anything, even a zip+4 database. That would be huge though. We have a lot of addresses in the US.

I found a service but I didn't follow it to the end to find out what it could actually provide as output. I would absolutely consider paying for a service to fix a one time conversion problem. It will be more reliable and quite possibly cheaper in the long run.

Address Standardization | What it is and how to do it. (smartystreets.com)

 

[Galaxiom]

Some use no commas, some use other characters, but at least most use the standard formula of: Street, City, State, Zip.

Then maybe you should consider the technique I mentioned in Post #4.

 

[Gasman]

I'm not a huge fan of the USPS
I ordered something in the USA for delivery to a a dive buddy, to bring out to Cozumel the other year.
I put my name on it, so he could spot it straight away.

The USPS identified that there was no such name at that address and just returned it to the company????

Pat, you are concerned about Google, but that event is just too Big brother for me.

 

[Pat Hartman]

Pat, you are concerned about Google, but that event is just too Big brother for me.

Big Brother is here. You can't walk a street in a large city without being on camera. You can't send an email from or to a gmail account without it being read by Google and monetized. If I send email to someone with a Google account I am NOT giving my permission for the email to be read and as a citizen Google should not be able to use my email for their purposes just because the recipient elected to give away their privacy. Banks simply give up information to the Feds without a warrant. Anyone in or around DC that used a credit card on Jan 6th is now on the FBI's radar.

Europe is going to have to save the first (free speech) and fourth (the right of the people to be secure in their persons, houses, papers, and effects, against unreasonable searches and seizures) amendments for America. Our Congress and Supreme Courts are so far gone we're lost unless you can get the big tech companies under control we're all doomed.

 

[Isaac]

@Eljefegeneo do you mind me asking where the data comes from?

 

[Eljefegeneo]

Isaac: I am searching the web based on a list of companies that might be interested in a certain service. The information is gleaned from either their web page or other related pages like Facebook, Linkedin, Manta, DnB, etc. So it really isn't in any special form. My code above allows me to copy an address from a web page, and then either paste it into a temporary field for automatic update to my Address, City, State, Zip fields if it is in the correct format, or paste it temporarily in my notes field so that I can quickly edit the address. Not rocket science, just plain hard work. I only wish the list was given to me with the addresses already in the spreadsheet so I could have manipulated it differently.

Some things you just to do the old tried and true way, cut and paste. I got a kick out of the diversion this post took. Sort of like a Next Door post where commenters got off topic. I got helped for which I am grateful.

But for me it is solved. Period.

 

[Isaac]

Isaac: I am searching the web based on a list of companies that might be interested in a certain service. The information is gleaned from either their web page or other related pages like Facebook, Linkedin, Manta, DnB, etc. So it really isn't in any special form. My code above allows me to copy an address from a web page, and then either paste it into a temporary field for automatic update to my Address, City, State, Zip fields if it is in the correct format, or paste it temporarily in my notes field so that I can quickly edit the address. Not rocket science, just plain hard work. I only wish the list was given to me with the addresses already in the spreadsheet so I could have manipulated it differently.

Some things you just to do the old tried and true way, cut and paste. I got a kick out of the diversion this post took. Sort of like a Next Door post where commenters got off topic. I got helped for which I am grateful.

But for me it is solved. Period.

Cool. Honestly I was just curious. I did a project once that sounds a little similar, a guy had a service where he sent marketing emails to people of different trades - painters, drywall, electricians, etc. We were gathering email addressess off of yellowpages and similar websites. I can sympathize with a 1000 different data formats! Best of luck