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

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

Download the Excel Files

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

Excel File Icon 2021 xlsx Combine Text – BEGIN.xlsx

Excel File Icon 2021 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.

Today's tutorial is inspired by comments from Prof YC and Mohamad on our YouTube channel asking if we can do the opposite.

YouTube Comment Question Combine Text - Prof YC and Mohamad

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

Combine Text using Ampersand in Formula

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

Combine Text using Ampersand in Formula, Last name first

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).

Combine Text using TEXTJOIN function

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.
Select columns in the power query editor
  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.
Merge Columns Window

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

Power Query Editor Preview with Merged Column

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.

Merge Columns Window Custom separator

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

Power Query Editor with merged column

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.

Close & Load Button

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

Modern Excel Blueprint Training Webinar Excel Campus Jon Acampora

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.

    • Hi Shimshon,
      Great question! I believe it’s 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 don’t think there are any downsides.

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

  • 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?

  • 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
    Generic filters
    Exact matches only

    JOIN US & LEARN EXCEL

    Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
    Plus weekly updates to help you learn Excel.

    Download the eBook

    Excel Shortcuts List

    keyboard shortcuts list banner

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

    Excel Shortcuts List

    Join Our Weekly Newsletter

    The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

    Join Our Free Newsletter

    About Me

    Jon Acampora Profile

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

    This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

    MVP_Horizontal_BlueOnly

    Free Excel Training Webinar Modern Power Tools

    >