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 TutorialWatch 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.
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.
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.
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.
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
You may also like
IF Formula Tutorial for Excel Everything You Need To Know
How to Prevent #SPILL Errors in Excel Tables
5 Ways to Find and Remove Blank Spaces in Excel
The LAMBDA Function Explained How to Create Custom Functions in Excel
June 16, 2021 at 7:25 am
Is there a reason that you did not suggest using the CONCATENATE function?
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?
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?
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??"
Download the eBook
Excel Shortcuts List
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
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...