Excel Split—Do the Data Split Shuffle

Joe needs your help.  He inherited an Excel file that should contain a mailing list, but the data is not properly split into columns...

The data looks like this:
In a single column is: Firstname space Lastname.
In another column is: Address
And in another column is: City comma space State space Zip.

What he wants, of course, is a column for Firstnames, a column for Lastnames, a column for Addresses, a column for Cities, a column for States, and a column for Zips.

You can get pretty close using the following methods, but you'll still have some clean-up of the data to do by hand.

1. First, make a copy of the file in case you accidentally make matters worse and have to start over!
2. Insert four columns after the column that has the names in it.
3. Then, select the data in the column that has the names.
4. Go to the menu and say Data > Text to columns. This will launch a wizard.
5. Accept the default "delimited" and hit "Next".
6. Clear out any checked boxes under the heading "Delimiters" and check "space" as the delimiter. Hit Next then Finish.

The drill is the same for the city-state-zip column. But first, make sure the four or so columns to the right of the city-state-zip column are empty - if they're not, insert five or so columns so the wizard doesn't overwrite anything.

There will still be some clean up to do by hand - some people might have last names like Van Buren that are two words, so Excel will split them into separate cells. Maybe some city names will vary in number of words. Fix all that by hand.

To get rid of the commas after the city names:

1. Insert a column on one side of the city column and enter this formula:
2. =LEFT(A1,LEN(A1)-1) , where A1 equals the first city in your sheet. The formula will display all the characters in cell A1 except the last one. Copy the formula down for all the cities.  (Learn more about this formula here.)

But you really don't have city names, yet... what you have are formulas that display the city names. You must change the formulas into the values produced by the formulas:

1. Select all of them and hit the Copy button.
2. Now go to the menu and say Edit > Paste Special (not Paste), and in the box that pops up, choose "Values".
3. Now you can delete the column that has the cities followed by commas.
4. Pat yourself on the back.
    

P.S.  Joe should consider using Microsoft Access to store this data.  Depending on the complexity of the data, and the number of rows, Access might be a better choice.  For more information contact Kimberly.Schenk@DataSchenk.com