The TEXTJOIN function in Excel is a powerful tool that allows you to concatenate, or join, multiple text strings from a range or array with a specified delimiter. It’s particularly useful when you need to combine text data from multiple cells, and you want to include a separator like a comma, space, or any other character between the joined values.
Syntax
| Function | Syntax | Description |
|---|---|---|
| TEXTJOIN | TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...) | Joins text strings using a specified delimiter, with the option to ignore empty cells. |
Example
Let’s consider a scenario where you have a list of first names, last names, and departments, and you want to combine them into a single string for each person.
| First Name | Last Name | Department | Combined Information |
|---|---|---|---|
| John | Doe | Sales | =TEXTJOIN(", ", TRUE, A2, B2, C2) |
| Jane | Smith | Marketing | =TEXTJOIN(", ", TRUE, A3, B3, C3) |
| Emily | Jones | HR | =TEXTJOIN(", ", TRUE, A4, B4, C4) |
In this example:
The TEXTJOIN Function uses the formula =TEXTJOIN(", ", TRUE, A2, B2, C2) to combine the first name, last name, and department into a single string, separated by a comma and a space.
Practice Exercise 1
Task: Use the data below to create a single string that combines the book title, author, and year of publication, separated by a semicolon (;) and a space. Use the TEXTJOIN function to achieve this.
Practice Exercise 2
Task: Given the following data, combine the city, state, and ZIP code into a single string, separated by a space. Use the TEXTJOIN function to do this.
Solution for Exercise 1
TEXTJOIN Function: Use =TEXTJOIN("; ", TRUE, A2, B2, C2) to combine the book title, author, and year of publication, separated by a semicolon and a space.
Solution for Exercise 2
TEXTJOIN Function: Use =TEXTJOIN(" ", TRUE, A2, B2, C2) to combine the city, state, and ZIP code, separated by a space.
These examples and exercises should help you understand how to use the TEXTJOIN function in Excel to efficiently combine text from multiple cells with a specified delimiter.