Using a quick formula to combine multiple Excel columns into one is much quicker than doing it manually. In this quick post, we’ll show two methods to do this: the ampersand formula or the CONCAT function. They are both simple, so you can choose any of the two. I will share the step-by-step procedure for both, and you can choose the one you find more convenient.
In this guide, We will use the example of the excel table shown in the screenshot below. I will use both the ampersand formula and the CONCAT function methods to combine the name and phone number column into one column that I named Full details.
Combine Excel columns using the Ampersand Formula
We will use the Ampersand formula to combine the name and phone number columns into one column. Below is the procedure for doing this.
- Step #1: Click in the first free cell of the “Full details” column and type = (equal sign).
- Step #2: Click the first cell in the first column that you want to combine. In this case, I will click the cell with the name Tom.
- Step #3: Type & (and sign)
- Step #4: Click the first cell in the second column and press enter. In my case, I will click the cell with the phone number 707232321. The entire formula for combining the two cells is =B2&C2
After hitting enter, the name and phone number in the first cells of the columns you want to combine will now go to one cell (Full details). To have the entire columns combined, click the bottom right corner of the first cell in the combined column you created and drag down.
Here’s the result:
Method 2: CONCAT Function
With this method, I will use the same table and columns that we used in the previous method. So, here is the procedure.
- Step #1: Click the first cell in the column where you want your combined data to go
- Step #2: Type =CONCAT(
- Step #3: Click the first cell in the first column you want to combine
- Step #4: Type , (comma)
- Step #5: Click the first cell in the second column you want to combine
- Step #6: Type ) and hit enter. So, the entire formula will be =CONCAT(B2,C2)
Here’s the result:
To have all cells in both columns combined, click the bottom right corner of the first cell in the combined column you created and drag down just like we did in the previous method.