Excel Combine Text from Multiple Columns

Excel Combine Text from Multiple Columns

by | Jan 18, 2022 | Beginner, Excel, Uncategorized

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.

Excel Basic Formulas: Sum, Average, Count, Max, Min

Excel Basic Formulas: Sum, Average, Count, Max, Min

by | May 16, 2016 | Beginner, Excel

Using Excel basic formulas can help simplify your life by doing sums, averages, counts, maxs, and mins of many numbers at the same time.

To follow along, go to the following link to download the Efficient Analyst Data Validation List Drop-Down Exercise Workbook:

Efficient Analyst Excel Basic Functions Sum Average Count Min Max

Excel Basic Formulas: Sum

The Sum formula is a great one to start with.  Excel formulas all start with an = sign, followed by the function name and a section in parentheses to provide ranges or arguments.  Start out by typing =sum(

Efficient Analyst Excel Basic Functions Sum 1

Sum requires a range of cells you want to add together.  You can type your range in, or you can select it using your mouse or input device.  In this case, we will select D2:D21.

Efficient Analyst Excel Basic Formulas Sum 2

Next up you can close the parenthesis, although newer versions of Excel will auto close it for you.

Efficient Analyst Excel Basic Functions Sum 3

Excel Basic Formulas: Sum, Average, Min, Max, and Count

Using the same method, you can calculate the average, minimum value, maximum value, and count:

Efficient Analyst Excel Basic Formulas Sum Average Min Max Count

Which if you were using the downloadable exercise workbook, will give you the following results:

Efficient Analyst Excel Basic Formulas Sum Average Min Max Count results

Here are some basic formulas, and what they do.

Sum Adds all the cells together and gives you the total sum.
Average Gives you the average of the selection you provide.
Min Returns the smallest value from the selection you provide.
Max Returns the largest value from the selection you provide.
Count Counts how many cells have values in them.

Additional Tips

  • In the bottom right of the screen, newer versions of Excel provide you with a Sum, Average, and Count by default, and can be configured by right-clicking on it to provide minimum, maximum, and numerical count.

Efficient Analyst Excel Basic Formulas Sum Average Count lower bar