There are many reasons why you may need to combine data in different columns in excel. For the purposes of this tutorial, I’ll demonstrate how to take a column of first names and combine it with the last names column.
Step 1. Create the column you would like to use.
In this example you will see that column A and B are the ones containing the first and last names. I like to create a column right next to where I’m working, especially if you happen to have a spreadsheet with many columns. Simply right click on the very top of column C, where the actual letter is, and select Insert from the menu that pops up or hit the “I” key on your keyboard. I’m a big fan of shortcuts and this one happens to be one I use all the time, so I recommend remembering it for next time.
Step 2. Select the first cell you want the names to be combined in. Copy and paste or type in the equation below and hit enter.
=CONCATENATE(A2, ” “, B2)
A2 is the first cell or first name in this example and B2 is the second cell. The ” ” is to create the space between the two. You can put any kind of separator within the quotation marks, depending on what type of info you’re combining.
Step 3. Select the cell you just worked in (do not highlight the text within the cell). Copy it.
Use Ctrl +C to copy the cell or right click on the cell and copy that way. You’ll see the dotted lines appear around the cell.
Step 4. Select the next cell down, hold in the Shift key and select the bottom cell of the column. Paste.
Select C3 hold in the Shift key and select the very bottom cell you’re working with. Paste using the Ctrl +V shortcut or right click on your selection and paste using the menu.
Now depending on what you plan to do with your excel spreadsheet, you may want to delete the original columns containing the names. WAIT. If you do that, you’d be removing the data those cells are referencing. If you already did that, hit Ctrl +Z and move on to this next step.
Not to worry! You can make the cells contain the result without the formula.
Simply select the cells in that column, or if you have a lot of rows, select the whole column by clicking on the letter at the top.
Copy by using Ctrl +C or right clicking.
Click back in your top cell and paste ONLY the values by right clicking and selecting paste value. It’s the clipboard with the “123” on it.
Now you can delete columns A & B without losing any data in column C.