Today, I’ll help you learn how to use TEXTJOIN in Microsoft Excel. Keep reading!

You need to use the TEXTJOIN function in Excel to merge text strings with a delimiter, like a comma, semicolon, etc. This function allows you to merge first names and last names in full names, user names, and email domains into complete email addresses, generate comma-separated lists of values, and so on.

In this Excel TEXTJOIN tutorial, I have presented and discussed a few real-time situations where you can utilize this function to save time and effort. Let’s get started!

Basic Text Concatenation With a Delimiter

Before you can start using the TETXJOIN function, let me introduce you to its formula syntax and explanation of the arguments:

• delimiter: The character(s) to insert between text values, like a comma, semicolon, dash, and so on.
• ignore_empty: TRUE ignores empty cells, while FALSE includes them.
• text1, text2, …: The text values, cell references, or ranges to be joined.

A limitation of this function is that it doesn’t allow joining different text strings using separate types of delimiters. You can join two or more text strings using only one type of delimiter.

Now, suppose you’ve got a dataset shown in the above screenshot. It lists first names, last names, and email domains in different columns. Now, you’d like to create a complete email address using these data. Your dataset doesn’t have to be exactly as shown in the above example, but it could be similar.

Here, you can easily use the TEXTJOIN function to join the first and last names using a delimiter like a period. Then, you can join the full name with the email domain with the @ symbol using the standard concatenation symbol, which is the ampersand (&).

So, let me show you the quick steps you can practice on your end:

Select the destination cell where you want to combine the text strings.

Enter the following formula syntax into the selected cell:

=TEXTJOIN(".",TRUE,A2,B2)&"@"&C2

All the cell references are the text strings that are being joined by this formula. These are dynamic, so you can use the fill handle for the rest of the cells.

Ensure you edit the cell references according to your own dataset.

Now, hit Enter to calculate the first cell.

Select this cell and drag it down the column to apply the formula to the rest of the cells in the same column.

That’s it! You’ve successfully used the basic TEXTJOIN function to join text strings.

Ignoring Blank Cells

The TEXTJOIN function comes with an argument to include or exclude empty cells. If you don’t use this argument appropriately, the function will show the delimiter at the end of the first text string. This won’t look professional in your reports.

So, you need to enable the ignore empty cells feature in this function to not to use the delimiter at the end of the first text string when there’s no other text string in the second cell.

Let me explain the problem below with an example:

Suppose you’re using the following TEXTJOIN formula syntax to merge first and last names:

=TEXTJOIN(".",FALSE,A2,B2) 

However, the Last Name column doesn’t have any text strings in it.

The result would look like the example shown below:

John.

The period at the end of the first name doesn’t look professional.

Instead, you can use the following formula to merge cell contents:

=TEXTJOIN(".",TRUE,A2,B2)

Since the empty cell detection is set to TRUE, Excel won’t show the delimiter, the period, at the end of the first text, even if the second text isn’t available.

Merging Data From Multiple Columns

Suppose you want to merge multiple text strings from more than two columns. You can speed up the process by simply refercning the starting and ending columns joined with a colon.

For example, if you need to join text strings from columns A, B, and C, simply use A2:C2 as the cell range reference in the first cell, considering A1, B1, and C1 contains column headers. This will automatically merge all the text strings in columns A, B, and C.

Let me show you the steps with an example below:

The dataset I’m using is the one shown above. There are text strings in three columns that I wish to join. These column headers are First Name, Middle Name, and Last Name. After joining the text strings, I want to generate the result in the Full Name column.

So, I entered the following formula syntax in D2:

=TEXTJOIN(" ",TRUE,A2:C2)

When you use this formula syntax, don’t forget to modify the cell range reference.

After pressing the Enter key, I get the merged text string in D2.

Then, I used the fill handle D2 and dragged it down to apply the same formula to the rest of the cells.

Excel generated the merged text in the Full Name column.

Adding a Prefix or Suffix

If you need to add a prefix or suffix to merged text strings, you can do that using the TEXTJOIN function. Let me show you how in easy steps:

Suppose you’ve got a dataset of octets of IP addresses in First Octet, Second Octet, Third Octet, and Fourth Octet. You wish to combine the values from these columns into an IP address and put the suffix IP: at the beginning of the concatenated values.

You can find above the structure of the dataset. You can start this process of joining text strings or numbers.

Create a column named IP Address and select the first cell below this column header.

Enter the following formula into this cell:

=TEXTJOIN(". ", TRUE, "IP: "&A2,B2,C2,D2)

In the above formula, "IP: " is linked to A2 using the ampersand (&) symbol, which is the built-in concatenation function in Excel. This code will add the IP: prefix to the complete IP address. Depending on the dataset you’re working on, you need to change the cell references.

Once done editing the formula, press Enter to calculate the cell. Use the fill handle and drag it down the column to apply the same formula to the rest of the cells in the IP Address column.

Excel will merge the values in the referred columns and add the prefix at the beginning of the final value or text string.

Concatenate With Line Breaks

Suppose you’ve got a list of customer addresses stored across multiple cells. You want to merge them into a single cell and properly format the address with line breaks. Here, you can use this modification of the TEXTJOIN function.

Instead of a text-based delimiter, you’ll use the line break code, CHAR(10), and the rest of the formula syntax will remain the same. The split texts in multiple columns will be concatenated and formatted with the linebreak code.

The above screenshot tells you about the structure of the sample dataset.

Select a destination cell where you wish to get the output. Click on the Wrap Text command in the Alignment block of the Home tab.

Now, enter the following formula into the selected cell:

=TEXTJOIN(CHAR(10),TRUE,A2:E2) 

Change the cell range reference in the above formula so that it can fetch data from your worksheet appropriately.

Hit Enter to calculate the cell.

You’ll see that Excel has merged the split texts into a line-break formatted address.

Comma-Separated List With Unique Values

Often, you need to create a list of comma-separated items from a vertical column containing data on products, merchandise, etc. In this situation, you can use TEXTJOIN with the UNQIUE function to concatenate only the unique values from the column or vertical list.

For example, your starting dataset could look like the one shown above.

Now, select any cell from a column where you wish to produce this concatenated list of unique values.

Copy and paste the following formula into the cell:

=TEXTJOIN(",",TRUE,UNIQUE(A2:A6)) 

Before using the formula as is, make sure that you’ve changed the cell range reference in the formula syntax.

Now, hit Enter to get the merged comma-separated list of items.

This is an alternative way of transposing a dataset in Excel.

Create a Dynamic Sentence

If you often need to generate product descriptions from an ever-changing list of products, you can use this method to automate the task. Here, I’ll show you how the TEXTJOIN function can help you dynamically change the product details in a pre-composed product details text.

Most of the sentence structure remains the same except for the product name and its features. Let me show you how below:

Suppose you’ve got a dataset that looks like the one shown above.

Select a cell where you’d like to create the dynamic sentence and enter the following formula into it:

=TEXTJOIN(" ", TRUE,
    "This", A2,
    "comes in", LOWER(B2),
    "and size", C2,
    "for just", D2, "."
)

Make sure you change the cell range references according to your source dataset. Also, you can change the text strings or words within the double-quoted fields in the formula syntax.

Once done configuring the formula, press Enter to calculate the cell.

Excel will merge the product details with fixed text strings to compose the dynamic sentence.

Now, the next time, you only need to change the details in the product features table. The sentence will be created automatically for the new product.

Conclusions

Now you know how to use TEXTJOIN in Excel in basic to advanced ways. Hopefully, this Excel TEXTJOIN function tutorial will help you automate various tasks in an Excel worksheet where the ultimate goal is to merge text strings or numerical values.

If the guide helped you learn a new Excel skill, use the comment box to share your acknowledgment. You can also mention any suggestions.