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
**Interested in learning Power Query in-depth? We are currently running a promotion on our Power Query Pro and Power BI course. Offer ends Thursday, February 23, 2023 at midnight PST. Enroll here.**
Watch the Tutorial
Download the Excel Files
I've included both the Before and After files for you to download and practice on, if you like.
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.
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.
- To combine the contents of cells using Power Query, start by going to the Data tab (Power Query tab for older versions of Excel).
- 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.
- Select the columns that you want to combine.
- Then select Merge Columns on the Add Column tab. That will bring up the Merge Columns Window.
- 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.
- You can also name the column from this window.
- 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.
Is there a reason that you did not suggest using the “CONCATENATE” function?
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:
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?
How can I combine two cells and the first two words of the third cell?