Today we will explore 3 ways of quickly combine text from multiple columns in Excel. In this example, we will combine the name of the City and the Country, while adding a comma in between. For example Ottawa, Canada.
To follow along, go to the following link to download the Efficient Analyst Excel Combine Text from Multiple Columns Exercise Book: https://efficientanalyst.com/wp-content/uploads/2022/01/Excel-Combine-Text-From-Multiple-Columns.xlsx
Combine Text from Multiple Columns using Flash Fill
Excel 2013 introduced a new feature called flash fill, which seems to be getting better with every new release. Using the Customers – Exercise Book tab, we will be filling in column E, titled Flash Fill.
![](https://efficientanalyst.com/wp-content/uploads/2022/01/Excel-Combine-Text-from-Multiple-Columns-step-1.png)
In cell E2, we will write the results how we would like to see them, in this case we will write Ottawa, Canada.
![](https://efficientanalyst.com/wp-content/uploads/2022/01/Excel-Combine-Text-from-Multiple-Columns-step-2.png)
Next, we will select cell E2, and you can either press Control+E to fill in the rest of the column automatically, or you can right-click and hold the square in the bottom-right, and drag your mouse to the bottom of the table, as we can see here:
![](https://efficientanalyst.com/wp-content/uploads/2022/01/Excel-Combine-Text-from-Multiple-Columns-step-3.png)
![](https://efficientanalyst.com/wp-content/uploads/2022/01/image-1024x754.png)
Select Flash Fill in the drop-down that appears. Excel’s AI will automatically fill in the rest.
![](https://efficientanalyst.com/wp-content/uploads/2022/01/Excel-Combine-Text-from-Multiple-Columns-step-4.png)
Combine Text from Multiple Columns Using the Concat() function
The Concat function can be used to combine data from multiple columns. In column F, we will do a quick formula to show how it works. We will start with concat(C2, B2), which will combine cells C2 and B2 together.
![](https://efficientanalyst.com/wp-content/uploads/2022/01/image-1-1024x123.png)
The end result is not quite what we want. As you can see, it squished both cells together, which we will fix next.
![](https://efficientanalyst.com/wp-content/uploads/2022/01/Excel-Combine-Text-from-Multiple-Columns-step-5-1024x168.png)
We will fix this by adding the text we want as a separator by using “, ” as follows: concat(C2, “, “, B2). As you can see, this formats the text as we expect it: Ottawa, Canada.
![](https://efficientanalyst.com/wp-content/uploads/2022/01/Excel-Combine-Text-from-Multiple-Columns-step-6-1024x170.png)
As always, you can drag the formula down by left-clicking the square in the bottom-right of cell F2 and dragging it to the bottom of the table to fill in the other cells automatically.
![](https://efficientanalyst.com/wp-content/uploads/2022/01/Excel-Combine-Text-from-Multiple-Columns-step-7-1024x277.png)
Combine Text from Multiple Columns Using &
The last method we will see today is the one I actually use the most, which uses the & instead of the concat() function. To begin, in G2, we will write =C2&B2. Again you will notice that we get OttawaCanada all bunched up together. We will fix this in the next step.
![](https://efficientanalyst.com/wp-content/uploads/2022/01/Excel-Combine-Text-from-Multiple-Columns-step-8-1024x152.png)
As we did with the concat() function, we will use “, ” to add a comma and a space between the City and Country, giving us the following formula: =C2&”, “&B2.
![](https://efficientanalyst.com/wp-content/uploads/2022/01/Excel-Combine-Text-from-Multiple-Columns-step-9-1024x144.png)
As always, you can left-click the square in the bottom right of the cell to drag down the formula to fill in the City, Country for the rest of the table.
![](https://efficientanalyst.com/wp-content/uploads/2022/01/Excel-Combine-Text-from-Multiple-Columns-step-10-1024x232.png)
Recent Comments