Buzz
Each of the 6 new Excel functions serves distinct purposes to boost your efficiency. Among them, the Textjoin function combines text strings into a merged text string.
Excel's Textjoin Function merges a supplied text string into a combined text string. Users can specify a delimiter to be added between individual text items, if needed.
Exploring TEXTJOIN in Excel
Syntax of the Textjoin function:
TEXTJOIN( [delimiter], [ignore_empty], text1, [text2], ... )
Where the parameters carry the following meanings:
[delimiter] - Optional delimiter, inserted between each text string. If omitted, no delimiter is used.
[Ignore_empty] - Optional logical value, determines whether empty cells are ignored. If omitted, the function defaults to TRUE (ignoring empty cells).
Text1, [text2], ... - One or more text strings (or an array of text strings) that you want to combine.
Important Notes:
- The Textjoin function in Excel can handle up to 252 text arguments.
- The result of the Textjoin function must not exceed 32,767 characters.
- Each provided text argument can be an array of text strings/values or a single text string/value.
- The Textjoin function was first introduced in Excel 2016, so it's not available in earlier Excel versions. It's also not available in Excel 2016 for Mac.
Excel Textjoin function is similar to the Concat function. Both functions combine text strings, with the main difference being that Textjoin accepts a delimiter, whereas Concat does not.
Some Examples of the Textjoin Function
- Example 1: Combining Simple Texts
Formula:
Result:
- Example 2: Combining Text and Date/Time
When combining text with dates or times in Excel, note that dates and times are stored as simple numbers in Excel. If you input a date or time directly into the Textjoin function, it will display as a numeric value in the resulting text string, not in date or time format.
To combine dates or times with text in Excel, the first step is to use the Text function to convert the date or time value into a text string. This is demonstrated in the example below:
Formula:
Result:
In the example above, note:
- Spaces within ' ' are considered as [delimiter], used to separate values in cells A1-C2.
- The Text function is used to convert the date value in cell C2 into a string, using the date format 'mm/dd/yy' (month/day/year).
Check on the Microsoft Office website for more examples of using the Textjoin function in Excel.
Errors in Textjoin Function Usage
If, during the use of the Textjoin function, you receive an error message, it could be due to:
#VALUE! - Error occurs if the result of the Textjoin function exceeds 32,767 characters.
#NAME? - Error occurs if you are using a version of Excel prior to Excel 2016, as these versions do not support the Textjoin function.
Here, Mytour has just introduced the TEXTJOIN function in Excel to help you handle character strings more precisely and efficiently. Alongside TEXTJOIN, there are other functions supporting string manipulation with various applications. You can refer to articles that compile string manipulation functions to understand how to use these functions.
If you have any inquiries or questions that need clarification, feel free to share your thoughts and comments in the comment section below the article. Mytour will respond to your questions as soon as possible.
