TEXTJOIN function in Excel to merge text from multiple cells

This tutorial demonstrates the Excel TEXTJOIN function's power in merging text strings, offering a flexible alternative to concatenation. It surpasses older methods by allowing customizable delimiters and handling of empty cells.

• Excel's TEXTJOIN Function
• Formula Examples: Joining Text in Excel
  • Concatenating Lists with Commas
  • Using Different Delimiters
  • Combining Text and Dates
  • Merging Text with Line Breaks
  • Conditional TEXTJOIN
  • TEXTJOIN with Multiple Criteria
  • Returning Multiple Lookup Matches as a Comma-Separated List
• Troubleshooting TEXTJOIN Errors

Understanding the TEXTJOIN Function

TEXTJOIN efficiently combines text strings from cells or ranges, inserting a specified delimiter between each value. It offers control over whether empty cells are included. This function is available in Excel 365, Excel 2021, and Excel 2019.

Syntax:

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

• Delimiter: (Required) The separator between combined values (e.g., ", ", ";", CHAR(10) for line breaks).
• Ignore_empty: (Required) TRUE to ignore blank cells; FALSE to include them.
• Text1: (Required) The first text value or cell range.
• Text2, ...: (Optional) Additional text values or ranges (up to 252).

Example: Combining Address Parts

Instead of: =CONCATENATE(A2, ", ", B2, ", ", C2)

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

This simplifies merging multiple cells.

Key Considerations for TEXTJOIN:

1. Excel Version: Only available in Excel 2019 and later. Use CONCATENATE or "&" in older versions.
2. CONCAT Function: Excel's CONCAT function offers simpler concatenation but lacks delimiter and empty cell handling options.
3. Number Handling: Numbers supplied as arguments are treated as text.
4. Empty Delimiter: An empty string ("") as a delimiter concatenates values without separators.
5. Argument Limit: Supports up to 252 text arguments.
6. Character Limit: The result is limited to 32,767 characters; exceeding this causes a #VALUE! error.

Practical Formula Examples

1. Creating Comma-Separated Lists:

Concatenate a vertical list (e.g., wins/losses): =TEXTJOIN(",", FALSE, B2:B6) (FALSE includes empty cells).

2. Using Multiple Delimiters:

Use an array constant or cell references for varying delimiters:

=TEXTJOIN({", "," "}, TRUE, A2:C2) (array constant)

or

=TEXTJOIN($F$3:$G$3, TRUE, A2:C2) (cell references)

3. Combining Text and Dates:

Convert dates to text using TEXT: =TEXTJOIN(" ", TRUE, A2, TEXT(B2, "mm/dd/yyyy"))

4. Adding Line Breaks:

Use CHAR(10): =TEXTJOIN(CHAR(10), TRUE, A2:B2) (Remember to enable "Wrap Text").

5. Conditional TEXTJOIN:

Combine IF for conditional merging: =TEXTJOIN(", ", TRUE, IF($B$2:$B$9=1, $A$2:$A$9, "")) (Array formula in Excel 2019).

6. TEXTJOIN with Multiple Criteria:

Use * as AND operator within nested IFs: =TEXTJOIN(", ", TRUE, IF(($A$2:$A$24=E2)*($B$2:$B$24=F2), $C$2:$C$24, "")) (Array formula in Excel 2019).

7. Multiple Lookup Matches:

Return all matches as a comma-separated list: =TEXTJOIN(", ", TRUE, IF($A$2:$A$12=D2, $B$2:$B$12, "")) (Array formula in Excel 2019).

Troubleshooting Errors:

• #NAME?: Function not supported in the Excel version or misspelled.
• #VALUE?: Result exceeds 32,767 characters or an invalid delimiter is used.

This comprehensive guide empowers you to harness the full potential of Excel's TEXTJOIN function for efficient text manipulation.

The above is the detailed content of TEXTJOIN function in Excel to merge text from multiple cells. For more information, please follow other related articles on the PHP Chinese website!