Use Text to Columns to clean your data

Many times on campaigns, we end up with data in Excel spreadsheets from all kinds of sources. The spreadsheets often have different formatting, and it can be hard to pull the information together in a usable and consistent way.

One I use a lot is Text to Columns, which takes information that’s all jammed in one column, and you want to break it out into multiple columns.

Example: You get a spreadsheet of event attendees, but first and last name are in the same cell. You need First Name in Column A, and Last Name in Column B. Time to use "Text to Columns"

Here's how:

1. Insert a column to the right the column containing your data. (If you have middle names or spaces in last names, insert an extra column to the right).
2. Highlight the column with your data (Click the column header, or click in your first cell, and press "Shift + Ctrl + Down Arrow" simultaneously to highlight the data in your column quickly).
3. Go to “Data” and click "Text to Columns."
4. Choose your data type, “Fixed Width” or “Delimited.” Most files are "Delimited." You will choose that 97% of the time.
5. Choose your delimiter. This is how it knows how to separate your information. If you have "Joe Smith" in your cells, a space is your delimiter. If you have "Smith, Joe" then the comma is your delimiter.
6. View an example. When you select the delimiter, Excel shows an example in the dialog box. You should see straight lines where you want the information to break into columns.
7. Click “Finish.” As long as the example looks right, click “Finish” and your data will move into the new columns. If Excel asks you, "Do you want to replace the cell contents?" there are extra 'delimiters' you didn't see. You should cancel, and insert more columns before finalizing.

If you’ve ever spent hours trying to clean up a spreadsheet after an event, you know how frustrating it can be. Next time, save yourself a headache with Text to Columns!

What do you use Text to Columns for? Share your Excel war stories!

Lizandra Vidal is Data and Constituent Services Manager at Planned Parenthood Federation of America, and an NOI community member

Image from Tom Raftery and released under a Creative Commons License