Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Oct 2009
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Issue: Have a field that contains full address information (Addressline 1, Addressline2, city, state, zipcode) in one field. The data is not consistant, an entry may contain 1446 Woody Lane Somewhere,NY 00000 or 1446 Woody Lane Suite1D Somewhere,NY 00000. The only comma that is used seperates the City from the state. Question: How can I seperate the data and create seperate columns for Addressline 1, Addresslin2, City, State, Zip??? Thanks for your help in advance!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Welcome to the Lounge!

    It would be possible to extract the state and zip code, but not to split the part before the comma, because we don't know how long each part will be. For example, the street name could consist of two words ("Orchard Lane") or three ("Apple Orchard Lane") or even more, and the city name could be a single word ("Hackensack") or two ("Baton Rouge") or perhaps more, etc.

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    [quote name='mari' post='797914' date='14-Oct-2009 09:00']Issue: Have a field that contains full address information (Addressline 1, Addressline2, city, state, zipcode) in one field. The data is not consistant, an entry may contain 1446 Woody Lane Somewhere,NY 00000 or 1446 Woody Lane Suite1D Somewhere,NY 00000. The only comma that is used seperates the City from the state. Question: How can I seperate the data and create seperate columns for Addressline 1, Addresslin2, City, State, Zip??? Thanks for your help in advance![/quote]
    I have used the Excel capability with Data/Text to Columns where it was a single record. The advantage with Excel is that you can tinker and try various separator columns and then click and drag data as appropriate. Of course that only works well if you have 32000 rows or less. (Later versions of Excel will do more rows, but it gets to be a practical limit as far as doing things with that many rows.) I would separate on the comma first, and then separate on space characters. Then you can reassemble the Street Address once you get things in the right columns.
    Wendell

  4. #4
    New Lounger
    Join Date
    Oct 2009
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='797925' date='14-Oct-2009 11:26']Welcome to the Lounge!

    It would be possible to extract the state and zip code, but not to split the part before the comma, because we don't know how long each part will be. For example, the street name could consist of two words ("Orchard Lane") or three ("Apple Orchard Lane") or even more, and the city name could be a single word ("Hackensack") or two ("Baton Rouge") or perhaps more, etc.[/quote]


    Thank you for the Welcome!!!!

    Thank you, in my search I did find the ability to separate data from left to right using the Left$ function for the State and Zipcode. Yes you are correct since the data is a changing variable anything before the comma would be difficult to pull, I was hoping for a magical function.
    I thank you for your time and support!

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    For what it's worth, here is how you can extract the state and zip code. I have assumed that the field containing the full address is named Address.
    - Create a query in design view based on the table.
    - Add the fields that you want to see to the query grid.
    - Create a calculated column:

    P: InStr([Address],",")

    - Next to it, another calculated column:

    Q: InStr([P]+2,[Address]," ")

    - Then to extract the state:

    State: Trim(Mid([Address],[P]+1,[Q]-[P]))

    - And to extract the zip code:

    Zip: Mid([Address],[Q]+1)

    - If you want to extract the part before the comma as a single string:

    StreetEtc: Left([Address],[P]-1)

    (But it's probably better to use Excel to split this part, as suggested by WendellB)

  6. #6
    New Lounger
    Join Date
    Oct 2009
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='WendellB' post='797931' date='14-Oct-2009 11:41']I have used the Excel capability with Data/Text to Columns where it was a single record. The advantage with Excel is that you can tinker and try various separator columns and then click and drag data as appropriate. Of course that only works well if you have 32000 rows or less. (Later versions of Excel will do more rows, but it gets to be a practical limit as far as doing things with that many rows.) I would separate on the comma first, and then separate on space characters. Then you can reassemble the Street Address once you get things in the right columns.[/quote]


    Thank You WendellB, I attempted that too. If my data were slightly consistant it would probably work. Because the data has so many variables, not even the spaces can be used to seperate the data. I thank you for your help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •