Street Address with alphanumeric

[ampstar]

Hi All,

I have a database that I inherited that has street address backwards and with spaces.



Example1. E SEASIDE ST 64 1/2
Example2. MAIN ST 154
Example3. BOSTON POST RD 25

There are about 50,000 records and I am able to create a function to take out the whole numeric number like 62 or 154 but have issues with street names and streets that are like E for east W for west. I am not the greatest at Access and I am still learning so any help or insight would be great.

[June7]

And what about numbered streets:

64th Ave 213A

Are you trying to extract and populate another field?

Parsing text is common topic. With inconsistent structure, NO code can catch everything, too much variability. Program for the situations you know and can fix without corrupting data and then case-by-case evaluate the rest.

[ampstar]

I only have a few streets that are numeric street names (about 20), so I can handle those by hand. I can handle the ones with 1/2 in street address too. Its the bulk remainder I have an issue with.

[June7]

Fixing bad data can be a long and tedious and repetitive exercise. But hopefully you only have to do it once.

Filter dataset for known issue, such as the / character. Fix them.

Repeat for next issue.

[ampstar]

I was able to work around most of the data by using the follow query Street_Num: Mid(Place!Street,InStrRev(Place!Street," ")+1) to get street number and Street_Name: Left(Place!Street,InStr(Place!Street," ")+3) to get street name. It works for about 90% of my records, but if anybody else can give me a better way I welcome it. Thanks