TEXTJOIN combines text from multiple cells into one, with a specified delimiter (such as a comma, space, or any other character) between each piece of text. TEXTJOIN function is useful for creating lists, combining data, or formatting text with specific separators.
Syntax of TEXTJOIN Function in Excel
=TEXTJOIN(delimiter, ignore_empty, text1, text2,...)| Argument | Description |
|---|---|
delimiter(required) | This can be any character or string (e.g., space " ", comma ",", or even a hyphen "-"). |
ignore_empty(required) | TRUE to ignore empty cells; FALSE to include them. |
text1,text2,... | Text strings or ranges you want to join. (text1 – required, other – optional) |
The TEXTJOIN function can handle 252 text arguments.
Note: The TEXTJOIN function can ignore empty cells.
Examples of TEXTJOIN Function
Combine Multiple Names
Combine multiple names into a single line, each separated by a comma.
- Formula:
=TEXTJOIN(", ",TRUE,B2:B5) - Description: It combines all four names, ignores empty cells (if any), and separates each of them by a comma and a space.
- Output:
Alex, Jamie, Taylor, Gordon
Creating Addresses with a Space
Combine Split address into a single line separated by a space.
- Formula:
=TEXTJOIN(" ",TRUE,A2:D2) - Description: It combines all four parts of the address, ignores empty cells (if any), and separates each of them by a space.
- Output:
123 Main St Seattle 98101
Note: TEXTJOIN results are limited to 32,767 characters.
TEXTJOIN vs CONCAT
While both TEXTJOIN and CONCAT help combine text, TEXTJOIN offers the following advantages:
- Delimiter Inclusion:
TEXTJOINlets you specify a delimiter between joined text elements, whileCONCATrequires manual insertion. - Ignoring Empty Cells:
TEXTJOINcan skip empty cells, unlikeCONCAT, which includes all selected cells. - Ease of Use: With
TEXTJOIN, you can reference an entire range instead of individual cells, making it faster and more flexible.
Common Errors While Using TEXTJOIN
VALUE! Error
This error often occurs if any of the referenced ranges contain an error (e.g., #N/A or #DIV/0!). TEXTJOIN cannot handle errors within the referenced cells.
Incorrect Delimiter or Syntax
The delimiter argument should be in double quotes (e.g., “, ” for a comma and space). Not using quotes or incorrect placement of commas will lead to syntax errors.
Mismatched Data Types
If numeric values are formatted as text or vice versa, TEXTJOIN might not display them as expected.
TEXTJOIN Function – Tips and Best Practices
- Ignore Blank Cells: Use
TRUEas the second argumentignore_emptyto skip empty cells, ensuring cleaner results without extra delimiters. - Filter with Conditions: Combine TEXTJOIN with IF to include only specific values based on criteria, like joining only cells above a certain number.
- Avoid Text Truncation: Stay within the 32,767 character limit by grouping large datasets or using multiple
TEXTJOINfunctions if necessary.
Conclusion
The TEXTJOIN function in Excel simplifies the process of combining text from multiple cells, with options for handling delimiters and ignoring empty cells. It makes merging names, creating custom lists, or organizing data easier. Learning to employ TEXTJOIN can significantly streamline your workflow, especially when dealing with large datasets or detailed text formatting tasks.
Frequently Asked Questions (FAQs)
-
What is the Syntax of TEXTJOIN Function in Excel?
=TEXTJOIN(delimiter, ignore_empty, text1, text2,…) is the syntax of TEXTJOIN in Excel.
-
Does TEXTJOIN skip blank cells in a range?
Yes, the TEXTJOIN function skips blank cells if the second parameter
ignore_empty, is set toTRUE. -
Can TEXTJOIN be used with non-contiguous cells?
Yes, the TEXTJOIN function can be used with non-contiguous cells or ranges, but each must be separated by a comma (e.g.,
=TEXTJOIN(", ", TRUE, A1, C1:C5, E1)). -
Is it possible to use multiple delimiters in TEXTJOIN?
No, TEXTJOIN allows only one delimiter for each function use. However, you can nest additional TEXTJOIN functions if you need multiple delimiters.
-
Does TEXTJOIN Function Works with Arrays?
Yes, use ranges or arrays as arguments, and TEXTJOIN will handle them seamlessly.
