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.
In cell E2, we will write the results how we would like to see them, in this case we will write Ottawa, Canada.
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:
Select Flash Fill in the drop-down that appears. Excel’s AI will automatically fill in the rest.
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.
The end result is not quite what we want. As you can see, it squished both cells together, which we will fix next.
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.
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.
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.
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.
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.
Recent Comments