When you’re working with spreadsheets, you’ll often find yourself working with data spread across multiple columns. But on occasion, you may want to join the values from two columns into one. Fortunately, this is easy to do, and you won’t have to retype any data. Let’s learn how to combine two columns in Excel.
How to Combine Two Columns in Microsoft Excel (With the CONCAT Function)
Excel offers two great ways to combine two columns into one. First, let’s consider an example set of data. Imagine that you have two sets of numbers that you want to combine into a single column. A good option in this case is to use Excel’s CONCAT function.
CONCAT simply joins the contents of two (or more) columns together. It does have key limitations, however. If you want to add spaces, commas, or other values – called delimiters in Excel – you’ll need to use the & formula example found below in this tutorial.
If, however, you want two columns joined directly together, CONCAT is a quick and easy choice. Let’s say that you have data in columns A and B, and want to combine them together inside column C. To start, click into the first empty cell in column C. Here, that’s cell C1.
Then, type an = sign on your keyboard. When you place an = sign in a spreadsheet cell, you’re telling Excel that you’ll be inputting a formula. Continue by typing CONCAT(. So far, your CONCAT function formula reads:
=CONCAT(
Then, click into your first column’s first cell, A1. Type a comma, then click into the second column’s first cell, B1. Then, close your parentheses. Your complete CONCAT function is:
=CONCAT(A1,B1)
Hit Enter on your keyboard, and Excel will return the combined values of A1 and B1 into cell C1. From here, you can then click and drag cell C1 downward by grabbing the lower right corner. Excel will automatically update your formula to combine the remaining data.
How to Combine Two Columns in Microsoft Excel (With the & Formula)
Now, imagine that you have a list of cities and states. Using the CONCAT function would deliver combined words – think LincolnNebraska and Las VegasNevada. Chances are, you’d rather have a comma and space between the city and state. Here, you can use & signs in your formula bar to combine two columns, while adding custom delimiters in between the values.
Once again working with two columns, click into the first empty cell in column C. Type an = sign once again. This will also be a formula, but it won’t involve the CONCAT function due to its lack of delimiter control.
Next, click on the first column cell containing the name of a city. Here, that’s cell A1. Then, type an & sign. Your formula reads:
=A1&
Now, it’s time to insert your delimiters. These are always indicated by double quotation marks, “. Type “. Then, think about what you want to be placed between the contents of your two column cells. Once again, you want a comma, followed by a space. Type a comma, then a space, and then a second double-quotation. Your formula reads:
=A1&”, "
To finish the formula, type another &, then click on cell B1. Your complete formula is now:
=A1&", "&B1
Hit Enter, and Excel will combine the first cell in your city and state columns, with the addition of your space and comma. Once again, you can then copy the formula downward to capture and delimit the remaining data in your two columns.
As you can see, Excel makes it easy to combine two columns. This is a great way to work with your data – no retyping required.