Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2002
    Indianapolis, Indiana
    Thanked 0 Times in 0 Posts

    Parsing Problems (Excel 97 SR2)

    I am using Access-Excel 97 SR2 at work, and Access-Excel 2000 at home. I have a large Excel file where the addresses are all in one field (Address). Here are some examples:

    807 E.South St. Apt.33 Crown Point, IN 46307
    5269 Cedar Point Dr. G152 Crown Point, IN 46307
    717 Moraine Trace #16 Schererville, IN 46375
    8128 Mount Ct. #A Crown Point, IN 46307

    I need this data broken into these fields:
    Address = Street Address

    I have phone numbers that need the dashes stripped out.
    I have dates in the format mm/dd/yy that need to be mm/dd/yyyy.
    I have names in Last, First format in one field...need these to be split also.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Pittsburgh, Pennsylvania, USA
    Thanked 342 Times in 335 Posts

    Re: Parsing Problems (Excel 97 SR2)

    Some thoughts:

    Data , text to columns on the address, delimited, mark comma, to get out "address/city" and "state/zip"
    Parse the state/zip with text to columns with space delimiter

    Address/city is going to be more manual. I suggest making some temp cols
    B1 = Len(a1)/2
    Find the space when from the halfway point
    eg C1 =find(" ",A1, B1)]
    Make D1 = left(A1, C1-1) ; E1 = Mid(A1,C1+1, 255)
    Sort on B1 and and browse through to see if they parsed ok. as you browse if you need to go to an "earlier or later space" change the entry in B1 (guess for earlier, for later use value in C1+1

    Once D1 and E1 are correct Paste-special values and delete the temp columns.

    Strip dashes in phone# use find replace, replace all "-" with nothing
    Change date format (format cells custom mm/dd/yyyy)
    Last, first name use data text-to-column, dleimited, mark comma


Posting Permissions

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