Excel Training London: textjoin

The TEXTJOIN function in Excel is a powerful tool for combining text from multiple ranges or strings, with the option to include a delimiter between each text item. Below are 10 examples of how you can use the TEXTJOIN function, including scenarios with data and formulas.

Syntax:

TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

delimiter: The character or string to insert between each text item.
ignore_empty: TRUE to ignore empty cells, FALSE to include them.
text1, text2, ...: The text items or ranges to join.

Example 1: Basic Text Joining

Combine text from cells A1, A2, and A3 with a comma and space as the delimiter.

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

If A1 = "Apple", A2 = "Banana", A3 = "Cherry", the result is:

Apple, Banana, Cherry

Example 2: Joining a Range of Cells

Combine text from a range (A1:A5 ) with a hyphen as the delimiter.

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

If A1:A5 contains ["Red", "Green", "Blue", "Yellow", "Orange"], the result is:

Red-Green-Blue-Yellow-Orange

Example 3: Ignoring Empty Cells

Combine text from a range (A1:A5 ), ignoring empty cells.

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

If A1:A5 contains ["Cat", "", "Dog", "", "Fish"], the result is:

Cat, Dog, Fish

Example 4: Including Empty Cells

Combine text from a range (A1:A5 ), including empty cells.

=TEXTJOIN(", ", FALSE, A1:A5)

If A1:A5 contains ["Cat", "", "Dog", "", "Fish"], the result is:

Cat, , Dog, , Fish

Example 5: Combining Text with Formulas

Combine text from a range (A1:A3 ) and add a calculated value.

=TEXTJOIN(", ", TRUE, A1:A3, "Total: " & SUM(B1:B3))

If A1:A3 contains ["Apples", "Oranges", "Bananas"] and B1:B3 contains [10, 20, 30], the result is:

Apples, Oranges, Bananas, Total: 60

Example 6: Nested TEXTJOIN

Combine text from two ranges (A1:A3 and B1:B3 ) with different delimiters.

=TEXTJOIN("; ", TRUE, TEXTJOIN(", ", TRUE, A1:A3), TEXTJOIN(", ", TRUE, B1:B3))

If A1:A3 contains ["Red", "Green", "Blue"] and B1:B3 contains ["Circle", "Square", "Triangle"], the result is:

Red, Green, Blue; Circle, Square, Triangle

Example 7: Combining Text with Line Breaks

Combine text from a range (A1:A3 ) with line breaks as the delimiter.

=TEXTJOIN(CHAR(10), TRUE, A1:A3)

If A1:A3 contains ["First", "Second", "Third"], the result is:

First
Second
Third

(Ensure the cell is formatted to wrap text.)

Example 8: Combining Text with Conditional Logic

Combine text from a range (A1:A5 ) only if the corresponding value in B1:B5 is greater than 10.

=TEXTJOIN(", ", TRUE, IF(B1:B5>10, A1:A5, ""))

If A1:A5 contains ["A", "B", "C", "D", "E"] and B1:B5 contains [15, 5, 20, 8, 12], the result is:

A, C, E

Example 9: Combining Text with Dates

Combine text from a range (A1:A3 ) with formatted dates.

=TEXTJOIN(", ", TRUE, A1:A3, TEXT(TODAY(), "mm/dd/yyyy"))

If A1:A3 contains ["Task 1", "Task 2", "Task 3"] and today's date is 10/25/2023, the result is:

Task 1, Task 2, Task 3, 10/25/2023

Example 10: Combining Text with Dynamic Ranges

Combine text from a dynamic range (A1:A *) where the range size is determined by a formula.

=TEXTJOIN(", ", TRUE, A1:INDEX(A:A, COUNTA(A:A)))

If A1:A4 contains ["One", "Two", "Three", "Four"], the result is:

One, Two, Three, Four

These examples demonstrate the versatility of the TEXTJOIN function in Excel for combining text, handling empty cells, and integrating with other formulas.

Excel Training London

Thursday, 30 January 2025

textjoin