Our third lesson in the 4 part Name Badge Workshop is about how to combine names that are in two columns in Excel into one.
Let’s say that this time your client only wants one line of personalization on each badge, just a first and last name. When they send you their name list in Excel and you open it you find, not the names all in column A, but instead with first names in column A and last names in column B. There is a fast and easy formula to getting those names into one column: concatenate.
From the start, your name list probably looks similar to this:
In the first empty cell on the first row (more than likely the first cell in column C) you’ll type the following:
=concatenate(A1,” “,B1)
Make sure that you include that [space] between the quotation marks.
What this formula does is tell the program to take the information that is in cell A1, add a space after it, then combine it with what is in cell B1.
After you’ve typed that, click on any other empty cell. You should see the formula disappear and the combined first and last name in that cell you were typing in. Click on the cell and then hover your mouse over the bottom right corner. When you do your cursor will change to a +.
Now, left click, hold and drag until you get to the last name.
Once you have now highlighted all of the boxes in column C next to all of your names you can let go. The empty cells will now fill in with the combined names. This isn’t quite the finish, though. What you have is really just a column of formulas, not the final editable names.
Now, click on the C at the top of the column to highlight the entire column. Right click on the C choose “Copy”.
Open up a new blank spreadsheet that will be your finished name list. Right click on the A to highlight the first column and choose “Paste Special”.
This will bring up a box. Choose the option “Values” and then click “OK”.
Now you have successfully pasted the combined names into a new sheet. Go ahead and save this spreadsheet and you’re ready to send these in!
For you folks who want to get more advanced with your concatenating skills:
Concatenate comes in handy in many situations where you need to combine columns. One instance is when you have City and State but in separate columns. If you need a fast way to combine the two with a comma and space in the proper places you can adjust the formula to:
=concatenate(A1,”, “,B1)
Whatever you put in the quotation marks is what will be put between the information. Remember to adjust the A1 and B1 to be whatever is in the first row of what you want to combine. For example, if you have a header row and your information is in A2 and B2, just plug those into the formula instead.
Our final lesson in the Name List Workshop will be later this week and we’ll go over how to fine tune your name list by removing spaces, changing case and other small details that can be overlooked.
Tags: badge, customer service, format, list, name badge, name list, name tag, promotion