Lesson#253: Excel TEXTJOIN Function: Unleashing the Power of Concatenation with Delimiters

When it comes to combining text in Excel, the TEXTJOIN function is a true game-changer. It empowers you to concatenate text values with ease, all while adding custom delimiters between them. In this expert-level guide, we’ll delve into the intricacies of the TEXTJOIN function, showcasing its significance and versatility. With more than 10 practical examples, you’ll be well-equipped to master the art of text concatenation with delimiters.

Understanding the TEXTJOIN Function

Before we dive into examples, let’s understand the core concepts of the TEXTJOIN function:

• TEXTJOIN Function: This function allows you to concatenate text from a range or individual cell references while specifying a delimiter to separate the text values.

The TEXTJOIN function is a versatile tool that can be used in various scenarios, such as merging names, creating comma-separated lists, or formatting addresses. Now, let’s explore practical examples to see how this function works.

Step-by-Step Tutorial

Example 1: Concatenating Text with a Comma

Let’s say you have a list of names, and you want to concatenate them with a comma as the delimiter.

Table: List of Names

Name
John
Mary
Robert
Lisa

Using TEXTJOIN Function:

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

In this formula:

• ", " specifies the comma and space as the delimiter.
• TRUE indicates that empty cells should be ignored.
• A2:A5 represents the range of names to concatenate.

The result will be: “John, Mary, Robert, Lisa.”

Example 2: Creating a Space-Separated List

Imagine you have a list of cities, and you want to create a space-separated list.

Table: List of Cities

City
New York
Los Angeles
Chicago
Miami

Using TEXTJOIN Function:

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

In this formula:

• " " specifies a space as the delimiter.
• TRUE indicates that empty cells should be ignored.
• A2:A5 represents the range of cities to concatenate.

The result will be: “New York Los Angeles Chicago Miami.”

Example 3: Concatenating Text with a Line Break

Suppose you have a list of sentences, and you want to concatenate them with a line break (new line) as the delimiter.

Table: List of Sentences

Sentence
Excel is a powerful tool.
It simplifies data analysis.
You can create amazing charts.

Using TEXTJOIN Function:

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

In this formula:

• CHAR(10) represents a line break character.
• TRUE indicates that empty cells should be ignored.
• A2:A4 represents the range of sentences to concatenate.

When you press Enter, the result will display with line breaks:

Excel is a powerful tool.
It simplifies data analysis.
You can create amazing charts.

Example 4: Combining Text with a Custom Delimiter

Let’s say you have a list of email addresses, and you want to concatenate them with a semicolon and a space as the delimiter.

Table: List of Email Addresses

Email Address
john@example.com
mary@example.com
robert@example.com
lisa@example.com

Using TEXTJOIN Function:

=TEXTJOIN("; ", TRUE, A2:A5)

In this formula:

• "; " specifies a semicolon and a space as the delimiter.
• TRUE indicates that empty cells should be ignored.
• A2:A5 represents the range of email addresses to concatenate.

The result will be: “john@example.com; mary@example.com; robert@example.com; lisa@example.com.”

Example 5: Merging Text with Conditional Delimiters

Imagine you have a list of products, and you want to concatenate them with a comma and a space as the delimiter, but only if the cell is not empty.

Table: List of Products

Product
Apple
Orange
Banana

Using TEXTJOIN Function:

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

In this formula:

• ", " specifies a comma and space as the delimiter.
• TRUE indicates that empty cells should be ignored.

The result will be: “Apple, Orange, Banana.”

Example 6: Concatenating Text with a Slash

Suppose you have a list of file paths, and you want to concatenate them with a slash as the delimiter.

Table: List of File Paths

File Path
C:\Documents\Excel
D:\Reports
E:\Data\Analysis

Using TEXTJOIN Function:

=TEXTJOIN("\", TRUE, A2:A4)

In this formula:

• "\" specifies a slash as the delimiter.
• TRUE indicates that empty cells should be ignored.
• A2:A4 represents the range of file paths to concatenate.

The result will be: “C:\Documents\Excel\D:\Reports\E:\Data\Analysis.”

Example 7: Joining Text with a Hyphen

Let’s say you have a list of model numbers, and you want to concatenate them with a hyphen as the delimiter.

Table: List of Model Numbers

Model Number
X-1001
Z-2022
Y-123

Using TEXTJOIN Function:

=TEXTJOIN("-", TRUE, A2:A5)

In this formula:

• "-" specifies a hyphen as the delimiter.
• TRUE indicates that empty cells should be ignored.

The result will be: “X-1001-Z-2022-Y-123.”

Example 8: Concatenating Text with a Colon

Imagine you have a list of times, and you want to concatenate them with a colon as the delimiter.

Table: List of Times

Time
08:30 AM
03:45 PM
12:15 PM

Using TEXTJOIN Function:

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

In this formula:

• ":" specifies a colon as the delimiter.
• TRUE indicates that empty cells should be ignored.

The result will be: “08:30 AM:03:45 PM:12:15 PM.”

Example 9: Concatenating with a Custom Separator

Suppose you have a list of IDs, and you want to concatenate them with a custom separator “|##|” as the delimiter.

Table: List of IDs

ID
12345
67890
54321

Using TEXTJOIN Function:

=TEXTJOIN("|##|", TRUE, A2:A5)

In this formula:

• "|##|" specifies a custom separator as the delimiter.
• TRUE indicates that empty cells should be ignored.

The result will be: “12345|##|67890|##|54321.”

Example 10: Concatenating Text with a Tab

Let’s say you have a list of book titles, and you want to concatenate them with a tab character as the delimiter.

Table: List of Book Titles

Title
The Great Gatsby
Pride and Prejudice
To Kill a Mockingbird

Using TEXTJOIN Function:

=TEXTJOIN(CHAR(9), TRUE, A2:A5)

In this formula:

• CHAR(9) represents a tab character.
• TRUE indicates that empty cells should be ignored.

When you press Enter, the result will display with tab characters:

The Great Gatsby    Pride and Prejudice    To Kill a Mockingbird

Conclusion

The TEXTJOIN function is a powerful tool for concatenating text with custom delimiters in Excel. It allows you to efficiently merge text from ranges or individual cells while controlling how the text is separated. Whether you’re creating comma-separated lists, combining file paths, or formatting your data precisely, TEXTJOIN provides the flexibility you need to handle a wide range of scenarios. Incorporate these examples into your Excel skill set, and you’ll be well on your way to becoming a data manipulation expert.

Stay updated with Excelabcd. Join our WhatsApp channel.

https://whatsapp.com/channel/0029Va5mZ9V1nozBRRpKbj38