Results 1 to 6 of 6
  1. #1
    tmcrouse is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Apr 2009
    Posts
    169

    Create address, city, state, zip columns

    I have a file that was sent to me and the enduser put all 118k addresses in one column. An example of the data is below:

    memberaddress
    500 45th St S Saint Petersburg FL 337111424
    2224 Hinson Rd Dover FL 335276302
    1306 Mitchell St Lakeland FL 338012135
    310 NW 18th Ave Miami FL 331254534
    1610 S Orange Blossom Trl #12 Orlando FL Orlando FL 32805



    I tried doing a text to column in Excel and that just splits everything right after the space. I then thought I could import to Access and run a mid query but that did not work. Any advice?

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    no good way.
    They didnt use a delimiter
    They didnt use set col widths
    The didnt help you at all.

    but heres an XL macro that can help ...some...
    Code:
    Sub ParseAddr()
    Dim vAddr, vST, vZip, vWord
    
    
    Range("A2").Select
    While ActiveCell.Value <> ""
       vWord = ActiveCell.Value
       i = InStrRev(vWord, " ")
       vZip = Right(vWord, i)
       vST = Mid(vWord, i - 2, 2)
       vAddr = Left(vWord, i - 3)
       
       ActiveCell.Offset(0, 1).Value = vAddr
       ActiveCell.Offset(0, 2).Value = vST
       ActiveCell.Offset(0, 3).Value = vZip
       
       ActiveCell.Offset(1, 0).Select   'next row
    Wend
    End Sub

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    As ranman says, whoever sent you the file did not do you any favors.
    Did you ask for a certain format? Was there any specification/expectation of format?
    You can try his xl macro, but it will resolve many, but not all issues.

    You can see in the 5th example Orlando FL is duplicated in the record.
    I have never seen Saint Petersburg spelled out as such -- always St Petersburg.

    I think, and from experience, you will have to do some general parsing and then do a check manually.

    You can do InstrRev to get the Zip and State. You can also find a State/Zip table to validate your parsing.

    You can also do some searching to find street type abbreviations eg St Ave Way Rd Trl Hwy ...to help parse out streets etc, but you'll have to develop logic for N,E,S, W, NE, SE, NW,SW...

    Good luck -- been there.

  4. #4
    tmcrouse is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Apr 2009
    Posts
    169
    They got the file from someone else who pulled it this way and I asked if they could delim it but they said what is that. I sent an example and they were like the file came over to them with address this way. My gosh what a mess. I will try the macro. Thanks

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    If you post the file, I'll look at it and maybe have a few options.
    You will have to look for some patterns, and program to match/parse based on pattern.
    But there will always be exceptions.

    How important is the data?

  6. #6
    tmcrouse is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Apr 2009
    Posts
    169
    Its actually word than that. I took the file and because I see spaces I did a text to columns in Excel using space and this for example is one thing I found:

    1600 W 5th St Apt 95 1600 W 5th St Apt 95 Sanford FL 327711752

    Not sure what these people did with this file but they have duplicated addresses in the one column. What a mess. So, I solved my problem by the text to columns first. Then I assigned a column header and just started with A to S. Then I filtered S to not be null and did a concatention for address and then left city, state zip as they are. Now, as I was working through this ones like this I only concatenated the first address entry. Then I had things like Fort Lauderdale that were in 2 columns so that needed concatenated. I am done with the cleaning now but what a pain in the you know what.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. drop down selections (country/state/city)
    By fastforded in forum Access
    Replies: 11
    Last Post: 03-26-2015, 07:40 AM
  2. Replies: 2
    Last Post: 03-18-2014, 10:15 AM
  3. Texas City,State,Zip and Area Code
    By burrina in forum Sample Databases
    Replies: 0
    Last Post: 11-12-2012, 10:35 PM
  4. Normalizing various City/County/State combinations (w/out zip)
    By DorkyDuvessa in forum Database Design
    Replies: 2
    Last Post: 05-08-2011, 07:49 PM
  5. City, State Zip lookup
    By garywmcp in forum Access
    Replies: 1
    Last Post: 04-24-2011, 06:15 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums