# How to combine formulas in Excel

Formulas, Power Query

## 3 Ways to Combine Text in Excel Formulas, Functions & Power Query

June 15, 2021

Bottom Line: Learn how to use formulas, functions, and Power Query to combine multiple cells or columns into a single column and include separator characters.

Skill Level: Intermediate

## Watch the Tutorial

Watch on YouTube & Subscribe to our Channel

I've included both the Before and After files for you to download and practice on, if you like.

Combine Text BEGIN.xlsx

Combine Text FINAL.xlsx

## Combining Text from Multiple Columns

Recently, we've been looking at different ways to take information from one column and split it out into two or more columns. We used the example of a full name column that we wanted to separate into first and last names. We explored how to split columns using, Power Query, Text to Columns, Formulas, and Flash Fill.

These are great questions and I'm excited to show you three different ways to go about doing this.

## 1. Formula Using Ampersand (&)

Compatibility: All versions of Excel on all operating systems.

The first way to go about combining text is by using a simple formula. To join cells together we use the ampersand symbol (&). Joining the contents of cells A2 and B2 would look like this: =A2&B2.

But to separate the first name from the last name in the output, we use the space character wrapped in quotation marks and add another ampersand. The formula would read: =A2& &B2

Perhaps, you'd rather the final output has the last name first, then a comma, then the first name. If so, you can alter your formula to switch the cell order and add a comma before your space: =B2&, &A2

## 2. Formula using the TEXTJOIN Function

Compatibility: Excel 2019 or later including Microsoft 365 on all operating systems

What if you have three columns and not all of the cells have data in them? Certainly, you could add another cell into your formula with another ampersand, but anytime you had a blank cell, you would also have an additional space character in your output. To avoid this, you can use a formula with the TEXTJOIN function (available Excel versions 2019 and later).

The TEXTJOIN function has three arguments.

• The first argument is delimiter. This is the character (or string of characters) that you want to appear between the text in your cells. In our example of names, we want them separated by a space, so we type .
• The second argument is for ignoring empty cells. You choose either true or false, depending on if you want Excel to disregard cells that are blank. We do, so we will type TRUE.
• The third argument is text. These are the cells you want to combine. You can select them individually, or select an entire range.

All together, our formula is written: =TEXTJOIN( ,TRUE,A2:C2)

## 3. Power Query

Compatibility: Excel 2010 or later for Windows

The Merge Columns feature of Power Query is another great way to quickly combine multiple columns and add a separator character.

1. To combine the contents of cells using Power Query, start by going to the Data tab (Power Query tab for older versions of Excel).
2. Choose the option that says From Sheet or From Table or Range (depending on your version). That will open up a preview of your data in the Power Query Editor.
3. Select the columns that you want to combine.
1. Then select Merge Columns on the Add Column tab. That will bring up the Merge Columns Window.
2. Select your choice for how you want the text from each column to be separated. In our case, we want a space between the names.
3. You can also name the column from this window.
4. Hit OK.

A new column with the merged text will be added to the preview.

Power Query automatically detects and skips over any blank (null) cells when it combines the columns.

If you instead wanted the format to be Last, First in your merged column, the process is similar. The order in which you select your columns before selecting Merge Columns is the same order that they will appear in the output. Instead of selecting Space for your Separator, choose Custom, and then type a comma and a space.

When you hit OK, the new merged column will appear with the desired format.

If your preview looks the way you want it to, you can click on the top half of the Close & Load button to export it into your worksheet.

Note: This process I've described adds your new column to the existing columns on the output sheet. If you want to replace them instead, you simply choose the Merge Columns option on the Transform tab instead of the one on the Add Column tab.

If you're new to Power Query, here is a guide to installing Power Query if you are on an older version of Excel. And check out my free webinar below to learn how to get started with Power Query and the other modern Excel tools/features.

## Free Webinar on Power Query and Modern Excel

If you are relatively new to Power Query, I have a free webinar going on that we'd love to have you to join. It covers all the power tools, including Power Query, Power Pivot, Power BI, as well as macros, VBA, and pivot tables. You can access it here: The Modern Excel Blueprint

If you are interested in seeing another application of the TEXTJOIN function, check out this tutorial: 3 Ways to Display (Multiple Items) Filter Criteria in a Pivot Table

I hope you've learned a bunch and can put the knowledge to use! I want to help you be an Excel Hero in your workplace. If you have comments or questions, please leave them below.

Previous How to Reorder Multiple Columns in Power Query with Drag & Drop
Next How to Split Text in Cells Using Formulas

## The LAMBDA Function Explained How to Create Custom Functions in Excel

• Shimshon says:
June 16, 2021 at 7:25 am

Is there a reason that you did not suggest using the CONCATENATE function?

• Jon Acampora says:
June 18, 2021 at 12:27 pm

Hi Shimshon,
Great question! I believe its just a matter of personal preference. CONCATENATE or CONCAT do not have the option to specify a separator character like TEXTJOIN does. Therefore, you have to specify it for each occurrence of the separator.

So the CONCAT functions are really an alternative to the ampersand technique. The formula would be something like:

=CONCATENATE(A2, ,B2)

I tend to use the ampersand technique, and this could be because I use the same technique in other coding languages like VBA.

However, if you prefer to use CONCATENATE and find the formula easier to read, then I dont think there are any downsides.

Thank you for the question and suggestion! Have a great weekend!

• Diane says:
June 23, 2021 at 8:21 am

Using Power Query on the 3 Name Table, is there a way to get Last, First Middle without a comma space after the First name?

• blueboy714 says:
October 12, 2021 at 9:22 am

Regarding Power Query instructions. I have Excel 2013 Professional and do not see From Sheet or From Table or Range (depending on your version). That will open up a preview of your data in the Power Query Editor. in the Data tab.

What am I doing wrong or not seeing?

• Search

More results...

Generic filters
Hidden label
Exact matches only

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"

#### Excel Shortcuts List

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.