To quickly combine text and numbers from multiple cells in Excel, use the TEXTJOIN Excel function.
I’ll show you two examples, and you can scroll down to watch the video demo.
What is TEXTJOIN?
TEXTJOIN is an Excel function that combines values from two or more cells, and you can choose what to put between each piece — like a comma, space, or dash. It’s great for pulling together data quickly, into a single cell.
- Tip: See the TEXTJOIN help page on the Microsoft site, for information on which versions this function is available in.
In the two examples below:
- I’ll show a basic TEXTJOIN formula first, to combine data from several cells.
- After that, add a condition to the formula, so it combines specific items from the list.
Sales Reps and Regions
In both examples below, the data is a small list of sales reps and their regions.
- Headings are in cells A1:B1
- Names are in cells A2:A5
- Regions are in cells B2:B5
TEXTJOIN Syntax
The TEXTJOIN syntax has 3 required arguments, and you can include optional text arguments:
TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)- delimiter: Character(s) to use between the text items, such as a comma or semi-colon
- ignore_empty: To ignore empty cells, use TRUE. To include empty cells, use FALSE
- Tip: TRUE is the default, so you can leave this argument empty, if you want to ignore empty cells
- text1, [text2], …: Text items you want to join.
Simple TEXTJOIN Formula
First, I’ll use TEXTJOIN to make a list of all the rep names – all in one cell, separated by comma-space.
Here’s the formula in cell A7:
=TEXTJOIN(", ", TRUE, A2:A5)
The delimiter is a comma-space, ignore_empty is TRUE, and the text is in cells A2:A5
Names Combined
In cell A7, the four names are listed, in the same order they appear in the original list.
TEXTJOIN with Conditions
After you know the basics of the TEXTJOIN function, you can enhance it, by adding a condition to the TEXTJOIN formula.
In this example, I’ll make a list of only the reps who work in the “East” region, all in one cell.
Here’s my revised formula in cell E5, with an IF function creating the condition:
=TEXTJOIN(", ", TRUE, IF(B2:B5="East", A2:A5, ""))
How the Formula Works
Here’s how the revised TEXTJOIN formula works:
- First, the IF function checks each region name
IF(B2:B5="East", A2:A5, "")checks each Region.
-
- If the region is “East”, IF returns the Rep Name.
-
- If the region is not “East”, IF returns an empty string.
- After that, the TEXTJOIN function combines the results of the IF function
TEXTJOIN(", ", TRUE, ...)
-
- It separates the rep names, with comma-space
- It skips the blank cells, if there are any.
Tip: Put Condition on Worksheet
Instead of typing East in the TEXTJOIN formula, you could put East in a worksheet cell.
Then, in the formula, refer to the cell where you entered the region name.
For example, with the region name in cell D1, use this formula:
=TEXTJOIN(", ", TRUE, IF(B2:B5=$D$1, A2:A5, ""))
This makes it easy to change the condition, and show results for a different region.
Watch the Video
In this 3-minute video, Sarah shows how to add a condition with TEXTJOIN, so only the weekdays that are marked with X are included in the combined text.
Get the Workbook
To try the TEXTJOIN function, you can get the workbook that Sarah used in the video.
- Go to the TEXTJOIN page on my Contextures site,
- In the Download section, click the link to get the TEXTJOIN Examples workbook.
There are several other files on that page too, and more examples and videos to explore!
More Text Functions
There are many more text function pages on my Contextures site. Check these out, for tutorials, videos, and Excel files to download for practice.
Names, Split/Reverse First and Last
_________________
2 thoughts on “Combine Excel Text with Conditions – TEXTJOIN Function”