In Part 1 of our Excel for sourcing series we showed you how to combine data into one column. Now let’s say you want to do this in reverse. You have a worksheet, export or research list full of names that looks like this with the full name in one column.
But you need them in separate columns in order to import into your database, do a mass mailing or print name tags for an event. If your anything like most people you create a new column and start typing or copy and pasting. However time is of the essence in sourcing and that is just a waste of it. So we’re going to use the “Text To Columns” function found under the Data tab in Excel.
Sourcer Tip for Excel #2 – Text to Columns
Step One: Insert an empty column next to the column your names are listed in. If your list includes middle initials, suffix or prefixes you must take that into account and add the proper amount of columns. (i.e. John A. Smith would require the addition of 2 blank columns).
Step Two: Highlight the column with your data in it and click “Text to Columns” in the Data Tab.
Step Three: This will bring up a dialogue box like the one below. In box make sure the “Delimited” radio button is selected and click NEXT.
On the next page check the box for “Space” and uncheck the box for “Tab”. The preview window will display the results or your data. Click “Finish”.
Final Check: If you have not inserted enough blank columns for the data to separate into you may get an error like the one below. If you do, just click cancel and go insert another blank column. You will have to repeat the process but it only takes a moment and it’s better than losing the data currently in those columns.
In our next installment of the Excel Series you’ll see how both of the previous tricks can be put into play to create a list of corporate email addresses quickly and cleanly.