When you are working on an Excel spreadsheet, it might be possible that for any reason you need to merge columns. In our previous post, you learn how to join cells in an Excel sheet. Now, we’ll get to know how to combine columns in Excel using multiple methods.
Manually combining two or more columns will definitely take time and you will get bored of doing it. by using simple and quick tricks you can perform this task easily. Below we have explained some hacks you can use. Let’s dive into it.
How to Combine Columns in Excel using the CONCAT Function
CONCAT function is used basically to combine the text from multiple ranges. We’ll extract this feature and use it according to our needs. Let’s follow the steps given below:
Choose the cell in which you need to join two columns.
Put the formula in the cell. =CONCAT(Column1Cell, Column2Cell)
In the formula, you can add the first cell of column 1 to replace the Column 1 Cell and with Column 2 Cell you can add the first cell of column 2. According to this, the formula would be as:
=CONCAT(A2,B2)
Now, drag down the formula to the cell range as long as you want to.
And that’s it!
How to Combine Columns in Excel using the Ampersand Symbol
Follow the steps given below:
- Choose the cell in which you need to add the data.
- Now add =
- Click on the first cell you need to merge.
- Add & sign
- Click on the second cell you need to merge.
- Press the ENTER key.
- As per this method, the formula for combining cells A2 and B2 would be as:
- =A2&B2
On the other hand, when you need to merge columns vertically, below are the steps for this:
Choose a blank cell or column and put the following formula:
=IF(A3<>””,A3,INDIRECT(“B”&ROW()-COUNTIF(A$3:A$1000,”<>”)))
This formula will help in combining vertical columns.
Here is an alternative approach that you can follow as well:
Add the formula in the blank cell to combine columns between rows vertically:
=INDEX($A$2:$B$1000,ROW()/2.MOD(ROW(),2)+1)
Now, you can copy the formula downwards by using the fill handle given at the lower right side of the cell.
You will see the columns are merged vertically once the procedure is done.
Now, let’s see how you can combine columns in Excel using formulas.
If you need to merge columns horizontally, here is the formula combination for this:
=A3:A12&””&B3:B12 Legacy/CSE Version: {=A3:A12&” “&B3:B12}
=CONCAT(A3,” “,B3)
=CONCATENATE(A3:A12,” “, B3:B12) Legacy / CSE Version: {=CONCATENATE(A3:A12,” “,B3:B12)}
For vertical column combination, the formulas would be as:
=IF{A3<>””,A3,INDIRECT(“B”&ROW()-COUNTIF(A$3:A$1000,”<>”)))
=INDEX($A$2:$B$1000,ROW()/2,MOD(ROW(),2)+1)
How to Combine the Entire Column in Excel
When the formula is added to one cell, you can apply it to the entire column easily. Well, you don’t need to manually do it because entering the cell name one by one would take lots of time. For this, we have an easy-to-follow step:
Double-click the bottom right side of the cell in which you have added the formula. Or else you can left-click on the cell and drag the lower-right side of the cell in which you have added the formula down the column.
That’s it! you will see the entire column of your Excel spreadsheet is combined in just a few minutes.
Closing Thoughts
So, now you have learned how two or more columns can be combined by using different formula combinations. You must try these methods on the practice sheet so that you can learn them quickly.
