🌟 Part 1: Introduce
The TEXTJOIN function in Microsoft Excel allows you to concatenate multiple text strings from a range or an array using a specified delimiter between each text. It combines the CONCATENATE function’s functionality and the CONCATENATEX function’s delimiter feature. The TEXTJOIN function is handy when joining text strings with a specific separator or delimiter.
Purpose: The TEXTJOIN function is used to join multiple text strings with a specified delimiter.
Syntax & Arguments:
TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)
delimiter(required): This is the character or text string you want to use as a delimiter to separate the joined text strings.ignore_empty(required): A logical value (TRUE or FALSE) determining whether to ignore empty cells or text strings in the joining process. If set to TRUE, empty cells or text strings will be excluded from the result.text1, [text2], ...(required): The text strings or cell references that you want to join together.
Return value: The TEXTJOIN function returns a single text string that combines the specified text strings with the specified delimiter.
Remarks:
- The TEXTJOIN function was introduced in Excel 2016 and is available in Excel 2016 and later versions.
- The delimiter can be any character or text string, such as a comma, space, hyphen, etc.
- In the joining process, the function ignores non-text values, such as numbers or error values.
- If the
ignore_emptythe argument is set to FALSE, empty cells or text strings will be included in the result with the specified delimiter.
🌟 Part 2: Examples
Example 1: Concatenating Names with a Comma
Suppose you have a list of names in column A and want to join these names with a comma as the delimiter.
| A | B | |
|---|---|---|
| 1 | Name | Joined Names |
| 2 | John | =TEXTJOIN(“, “, TRUE, A2:A4) |
| 3 | Jane | |
| 4 | Sarah |
- In this example, the formula
=TEXTJOIN(", ", TRUE, A2:A4)cell, B2 joins the names from cell A2 to A4 with a comma and space as the delimiter. TheTRUEan argument ensures that empty cells in the range are ignored. - The result in cell B2 will be “John, Jane, Sarah”, as it concatenates the names in the range A2:A4 with the specified delimiter.
Example 2: Combining Text and Numbers
Suppose you have a list of products in column A and their corresponding prices in column B, and you want to join them with a hyphen as the delimiter.
| A | B | C | |
|---|---|---|---|
| 1 | Product | Price | Product-Price |
| 2 | Laptop | $999 | =TEXTJOIN(“-“, TRUE, A2:B4) |
| 3 | Smartphone | $699 | |
| 4 | Headphones | $149 |
- In this example, the formula
=TEXTJOIN("-", TRUE, A2:B4)cell, C2 combines the product names from column A and their corresponding prices from column B with a hyphen as the delimiter. - The result in cell C2 will be “Laptop-$999-Smartphone-$699-Headphones-$149” as it joins the product names and prices with the specified delimiter.
Example 3: Ignoring Empty Cells
Suppose you have a list of notes in column A and want to join them with a space as the delimiter, ignoring empty cells.
| A | B | |
|---|---|---|
| 1 | Note | Joined Notes |
| 2 | This | =TEXTJOIN(” “, TRUE, A2:A5) |
| 3 | is | |
| 4 | a | |
| 5 | sample |
- In this example, the formula
=TEXTJOIN(" ", TRUE, A2:A5)cell B2 combines the notes from cells A2 to A5 with a space as the delimiter. TheTRUEan argument ensures that empty cells in the range are ignored. - The result in cell B2 will be “This is a sample,” as it concatenates the non-empty notes in the range A2:A5 with the specified delimiter.
Example 4: Concatenating Employee IDs Suppose you have a list of employee IDs in column A and want to join them with a comma as the delimiter.
| A | B | |
|---|---|---|
| 1 | Employee ID | Joined IDs |
| 2 | 001 | =TEXTJOIN(“, “, TRUE, A2:A5) |
| 3 | 002 | |
| 4 | 003 | |
| 5 | 004 |
- In this example, the formula
=TEXTJOIN(", ", TRUE, A2:A5)cell B2 joins the employee IDs from cell A2 to A5 with a comma and space as the delimiter. TheTRUEan argument ensures that empty cells in the range are ignored. - The result in cell B2 will be “001, 002, 003, 004” as it concatenates the employee IDs in the range A2:A5 with the specified delimiter.
Example 5: Merging First Name and Last Name Suppose you have a list of first names in column A and column B, and you want to merge them with a space as the delimiter.
| A | B | C | |
|---|---|---|---|
| 1 | First Name | Last Name | Full Name |
| 2 | John | Doe | =TEXTJOIN(” “, TRUE, A2:B4) |
| 3 | Jane | Smith | |
| 4 | Sarah | Johnson |
- In this example, the formula
=TEXTJOIN(" ", TRUE, A2:B4)cell C2 combines the first names from column A and the last words from column B with a space as the delimiter. - The result in cell C2 will be “John Doe Jane Smith Sarah Johnson” as it merges the first names and last names with the specified delimiter.
Example 6: Creating a List of Products Suppose you have a list of products in column A and want to create a comma-separated list of products.
| A | B | |
|---|---|---|
| 1 | Product | Product List |
| 2 | Laptop | =TEXTJOIN(“, “, TRUE, A2:A6) |
| 3 | Smartphone | |
| 4 | Headphones | |
| 5 | Monitor | |
| 6 | Keyboard |
- In this example, the formula
=TEXTJOIN(", ", TRUE, A2:A6)cell B2 creates a comma-separated list of products from cell A2 to A6. TheTRUEan argument ensures that empty cells in the range are ignored. - The result in cell B2 will be “Laptop, Smartphone, Headphones, Monitor, Keyboard” as it combines the products in the range A2:A6 with the specified delimiter.
Example 7: Consolidating Comments Suppose you have a list of comments in column A and want to consolidate them into a single cell, separated by line breaks.
| A | B | |
|---|---|---|
| 1 | Comment | Consolidated Comment |
| 2 | This is comment 1. | =TEXTJOIN(CHAR(10), TRUE, A2:A5) |
| 3 | This is comment 2. | |
| 4 | This is comment 3. | |
| 5 | This is comment 4. |
- In this example, the formula
=TEXTJOIN(CHAR(10), TRUE, A2:A5)cell B2 consolidates the comments from cells A2 to A5 into a single cell, separated by line breaks. TheTRUEan argument ensures that empty cells in the range are ignored. TheCHAR(10)represents a line break character. - The result in cell B2 will be:
This is comment 1.
This is comment 2.
This is comment 3.
This is comment 4.
As it combines the comments in the range A2:A5 with line breaks.
These examples illustrate using the TEXTJOIN function nested with different functions to perform various tasks in a business setting. The function can be combined with other functions to customize the joining process and meet specific requirements, such as creating lists, merging text, consolidating data, and more.
🌟 Part 3: Tips and Tricks
- You can use cell references or text strings as arguments in the TEXTJOIN function. For cell references, ensure they refer to a range or an array of values.
- The delimiter can be any character or text string. You can experiment with different delimiters to suit your specific requirements.
- If you want to include empty cells in the result, set the
ignore_emptythe argument to FALSE. - Avoid circular references in the range the TEXTJOIN function provides, as it may cause errors or incorrect results.
- The TEXTJOIN function is helpful for merging text strings from different cells or ranges into a single, well-formatted text string.
These examples demonstrate how to use the TEXTJOIN function to join text strings with a specified delimiter, ignoring empty cells or text strings. The function provides flexibility in combining various data elements and is handy in multiple business scenarios, such as creating concatenated lists, generating email addresses, or formatting reports.