Sourcer and Recruiter Excel Tip – Concatenate

Editor’s Note: This week we are kicking off a three part series in which Jennifer Bowen will share three Microsoft Excel tips that will increase sourcing and recruiting productivity.

Most talent sourcers and recruiters have a love-hate relationship with Microsoft Excel.  It’s great for keeping track of lists, tracking candidates and metrics, and stashing names while doing name generation.  But it’s also just one more place to do data entry and can seem cumbersome at times.  I’m about to show you a reason to fall back in love.

There are three key tricks I use in Excel to speed up my list manipulation and generation. I’m going to share the first one with you here, and the others in subsequent posts.

The first reason to love Excel is the ability to manipulate long lists of data that you may have otherwise had to do manually.

Sourcer Tip for Excel #1 – Concatenate

We’ve all been there, we have a long list of research but things are in multiple columns (i.e. First Name and Last Name or City and State).  We want the whole name in one column or a City, State in one column.  How do most people tackle this? By beginning to type or copy and paste each cell. Not anymore.  With a simple Excel formula the tedious manual method is gone and you can have a quick clean list of names.  How?

We’re going to use a function called “Concatenate”.  This function can bring the contents of two or more text cells together into one.  i.e. Column 1: First Name + Column 2: Last Name = Column 3: First Name Last Name

Here are the steps to combine First Name and Last Name into one column. Let’s pretend this is your starting List:

Excel 1

Step One: Insert a blank column between First and Last Name.

Step Two: In the newly created column hit the Space Bar once and then copy that down all the rows. This leaves your spreadsheet looking like this:

Excel 2

Step Three: In Column D where we are going to put the full name we now enter the following formula:    =CONCATENATE(A2,B2,C2) and hit ENTER

Article Continues Below
Excel 3

Voila! Here is what you’ll end up with.  Now just copy that formula down the column and you’ve got a list of full names.

Excel 4

If you were doing this to combine address compenents like City, State then you would simply enter a comma with a space following it in your newly inserted column and follow the rest of the process.

REMINDER: The results in column D are currently the result of a formula so if you delete columns A-C without first copying and pasting Column D as “Text Only”, you’ll lose your results.

In Part 2 of this Excel Series I’ll show you how to do this in reverse, taking a list of full names dividing them into separate columns.

If you found this article helpful, please share with others. Thanks!

This post previously posted on SourcingHacks.com

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.

Topics

2 Comments on “Sourcer and Recruiter Excel Tip – Concatenate

  1. If you want to delete columns A:C, do these steps: Select column D. Copy with Ctrl+C, Right-click and choose Paste Values (In Excel 2010, the icon looks like a clipboard with “123” on it.

  2. If you don’t want to create a new blank column, you could also use the formula =A1&” “&B2 and get the same result. Note that’s a space between the two quotation marks.

Leave a Comment

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