Data Manipulation

Working with Lists

Combining two lists into one

Sometimes, you will have two lists which you wish to combine.  For example, you may have one list of first names, and another of last names.  Combining two strings into one is called CONCATENATION. Excel provides three ways to concatenate: 1. The Concatenate function:  =CONCATENATE(A1, B1) results in A1B1 2. The Ampersand (”&”):  =A1 & B1 results in A1B1 3. The Plus sign (”+”):  A1 + B1 MAY result in A1B1 Of the three options, the Ampersand is the preferred method, because a. The Concatenate function takes more keystrokes b. The Concatenate function is not as intuitive, so it’s harder to troubleshoot c. The Plus sign is not reliable - if the cells being combined all contain numbers, the Plus sign will add them, not Concatenate. For these reasons, we’ll only consider the Ampersand in our discussions of Concatenation in Excel. So, if you have two lists (first names and last names) and wish to have one list (first name, space, last name) follow these steps: 1. Put the two lists side by side: 2. In the third column (C, in this case), enter the following formula in row 4: =A4 & B4 3. As you can see in the graphic to the right, the cells in the formula are highlighted.  The space between the cell references and the Ampersand are for visual clarity only - they are not required. 4. Note that the box around C4 has a small square in the bottom right corner - this is called the “handle” of the cell, and is very useful for copying cell functions and values.
The “handle” of the cell
Unfortunately, the results are not quite what we wanted: It would be better to have a space between the first and last name:  Ambrose Faulkner.  To do that, we need to amend our formula slightly: Now, our formula has an added space, concatenated into the middle of “Ambrose” and “Faulkner”.  Remember this approach - it becomes VERY useful when you need to create long strings, which we will explore more thoroughly during the sections on Macros.  You can make a string of any size by simply concatenating other, smaller strings. And here is our final string: To complete our list, double-click on the handle of the box around cell C4, and the formula will fill down:
Hover over the handle until the cursor changes to a thick “+”, then either double click or drag down. +
As a final step, right-click the already-highlighted range of cells, and choose “Copy”, right-click again, and select “Paste special”, then “Paste Values 123”.  This step replaces the formula with the actual strings, so you can delete your original two rows (if you want) and you will still have your list of names, in the desired format.
Home Home Why subscribe? Why subscribe? Training Training Purchase Purchase My Account My Account Blog Blog