Sourcer & Recruiter Excel Tip #2 – Text to Columns

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.

Excel 5

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.

Excel 6

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.

Excel 7

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”.

Article Continues Below
Excel 8

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.

Excel Screenshot 8

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.

This post was originally shared on

Tips and Tricks image is from

Jennifer is an accomplished talent acquisition professional with a strong background in strategic sourcing and recruiting. She has worked as a sourcer, researcher, and recruiter for boutique executive search firms and Fortune organizations. She is currently a Senior Recruiter at Amazon. Connect with Jennifer on LinkedIn and Twitter @JennBowen.


1 Comment on “Sourcer & Recruiter Excel Tip #2 – Text to Columns

  1. Only if you have first and last name. In case of middle name existing random in your name list this tip is half of a solution.
    Better to use FIND function to return the ‘space’ count within text cell, then use this combined with MID function.

    Example: MID(A2,FIND(” “,A2)+1,1000) this returns the same result as “Text to columns”. Using the same approach it can be identified the middle name if exists.

Leave a Comment

Your email address will not be published. Required fields are marked *