Parsing Problems (Excel 97 SR2)

Topic

New Reply

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
City
State
Zip

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.

Reply | Quote

Replies

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

Steve

Reply | Quote