TEXTJOIN Excel function concatenates or joins values that are supplied as references, ranges, or constants and returns the result as text. The significance of the Textjoin function is that it can join the values with or without a delimiter, and it can optionally ignore empty cells. It is a new function that is available only in Office 365 and Excel 2019. Here we shall look into the TEXTJOIN Excel function, Formula, its syntax, the different arguments, Errors and other details.
TEXTJOIN Excel Function
The TEXTJOIN function concatenates multiple values together with or without a delimiter. TEXTJOIN can concatenate values provided as cell references, ranges, or constants, and can optionally ignore empty cells.
The TEXTJOIN function takes three required arguments: delimiter, ignore_empty, and text1. Delimiter is the text to use between values that are concatenated together and should be enclosed in double-quotes (“”). To use no delimiter, supply an empty string (“”). Ignore_empty is a Boolean (TRUE/FALSE) value that controls whether empty values should be ignored or added to the result. This is often set to TRUE to avoid delimiters with no content in the result from TEXTJOIN. Text1 is the first value to join together. This can be a cell reference, a range, or a hard-coded text value. Subsequent optional arguments, text2, text3, text4, etc. The maximum number of text arguments tha can be provided to join are 252.
TEXTJOIN Function Syntax
=TEXTJOIN(delimiter, ignore_empty, text1, …)
Arguments
delimiter – Seperator between each text
ignore_empty – its a boolean value whether to ignore empty cells or not. TRUE ignores empty cells, while FALSE doesn’t ignore empty cells.
text1 – The text item to be joined. A string, or array of strings, such as a range of cells, or cell references, etc.
text2 – [optional] The second text item to join.
Output or Result of TextJoin Function
All valid arguments passed to it are returned as a concatenated text.
Number Formatting will be Lost
When numeric values (dates, times, percentages, etc.) are passed as arguments to Textjoin function, their number formatting will be lost.
#NAME Error in Excel Textjoin Function
When a text is provided as an argument to TEXTJOIN function, such text should always be enclosed in double quotes. When the text is not enclosed in double quotes, Textjoin function returns a #NAME Error.
TEXTJOIN vs CONCAT
The key difference between TEXTJOIN and CONCAT Excel functions is that, TEXTJOIN accepts a delimiter to use when joining the values together, and there is no such possibility with CONCAT function. It should be noted that both CONCAT and TEXTJOIN functions accept a range of cells to join together, a functionality which is absent in CONCATENATE function.
Summary
- TEXTJOIN is a new function, available in Office 365 and Excel 2019.
- TEXTJOIN function accepts a delimiter as input, which can be used to join the text values together.
- A Boolean value can be passed as an argument to ignore or not ignore empty cells in a range of cells that are to be joined.
- TEXTJOIN Function will accept range as input argument in addition to cell references, and text strings.
- Maximum 252 values can be passed to TEXTJOIN function to concatenate.
- Number formatting of Numeric values will be lost when passed as arguments to Textjoin excel function.
- The ampersand character (&) is an alternative to TEXTJOIN. The result is the same.