How to understand the TEXTJOIN() function in Excel

Working with text can be notoriously challenging because it’s often unclear how to store the data. Should you combine first and last names in the same cell? What about city and state? Often, what works well for presentation isn’t optimal for long-term storage or analysis. The TEXTJOIN() function in Excel is an excellent tool for manipulating text strings, allowing you to merge them effectively through Excel formulas, particularly with dynamic array functions.

TEXTJOIN() lets you specify a delimiter to join text from multiple cells and can skip empty cells, offering more versatility than CONCAT(), which merely concatenates text without these options. This makes TEXTJOIN() especially useful for creating lists or sentences from data where control over separators and the handling of empty cells is crucial.

We’ll explore some examples below. You can download the exercise file to follow along:

Before we go through some examples, let’s quickly review the parameters of TEXTJOIN(). It requires a delimiter, which is the text string that separates the joined text, ignore_empty, a boolean value that decides whether to skip empty cells, and text1, which is the first piece of text or range to join. You can also include optional parameters like text2, text3, and so forth to add more text items or ranges to the join operation.

To gain a deeper understanding of the function and how it works, I recommend consulting Microsoft’s documentation. However, I’ve found that the best way to truly grasp what a function does is by looking at examples, so let’s dive in.

In our first example, we’ll use TEXTJOIN() to merge the contents of cells A1 through A3 into the string "Dog, Cat, Bird". By setting the delimiter to a comma followed by a space (“, “), we ensure there’s a clear separation between each word, making the result much more readable than if we had only used a comma without the space:

=TEXTJOIN(", ", TRUE, A1:A3)

Alright, now that we’ve covered the basics, let’s dive into more complex scenarios. The second argument of TEXTJOIN() determines whether to ignore empty cells, and in straightforward cases like our previous example, this setting doesn’t make much difference. Because the default is to ignore empty cells, we don’t even need to specify TRUE, we could just leave it blank; it’s a matter of preference.

Now, let’s explore how we can leverage additional text arguments for more sophisticated uses, like filtering data. For instance, suppose we want to join cells only if they contain fruits. We can use the second text argument to check if the cell content is a fruit. If it is, we include it in the join, otherwise, we leave it blank. Since empty cells are not included in the TEXTJOIN() result, this method works brilliantly for filtering.

=TEXTJOIN(", ", , IF(B1:B3="Fruit", A1:A3, ""))

We can expand on this formula to pull in data from multiple columns. Here, I’ll use ampersands (&) to string together information from columns B and C, but only for entries where the occupation is “Developer”. Finally, we’ll join all these combined strings using commas:

=TEXTJOIN(", ", TRUE, IF(C1:C4="Developer", A1:A4
         &" from " & B1:B4, ""))

Let’s expand this concept to search for multiple values within our data. Suppose I want to find and combine quantities for fruits that are either Green or Orange. We can use IF() combined with a plus sign (+) to check for these multiple conditions. Then, we’ll use ampersands (&) along with our chosen characters to format the output as desired.

If the conditions aren’t met, the result will be blank, and therefore, those entries will be ignored by TEXTJOIN().

=TEXTJOIN(", ", TRUE,
  IF((B1:B4="Green")+(B1:B4="Orange"), A1:A4
    &" ("&C1:C4&")", ""))

In this post, we’ve explored the TEXTJOIN() function in Excel, which goes beyond basic concatenation by letting you merge text from multiple cells with a custom delimiter and the option to skip empty cells. This capability unlocks numerous possibilities for data management, ranging from creating straightforward lists to handling more intricate conditional text combinations.

To close, I’d love to hear from you! Do you have any innovative ways you’ve used TEXTJOIN() in your work or projects? Or do you have any questions or suggestions on how you could apply this function to make your data processing more efficient? Please share your insights and questions in the comments below.