You are aware of joining or concatenating text values together in Excel using CONCATENATE function or Ampersand operator (&). But if you need to combine multiple strings or text values from multiple ranges then CONCATENATE function or Ampersand operator cannot do so. Excel TEXTJOIN function is introduced in Excel 2016 version to achieve this.
HOW TO USE EXCEL TEXTJOIN FUNCTION
Excel TEXTJOIN function combines multiple strings or text values from multiple ranges, with each value separated by a delimiter and with settings to ignore empty values.
Excel TEXTJOIN function is categorized as a String/Text Function and is newly introduced in Excel 2016 version. It can combine maximum of 252 text arguments or can return up to 32,767 characters.
The syntax for the Excel TEXTJOIN function is:
=TEXTJOIN( delimiter, ignore_empty, text1, [text2], ...)
Excel TEXTJOIN function uses the following arguments:
Delimiter (required): It is the string that is used to separate each value in resulting string. It can be a space character, a comma, or double quotation mark etc.
Ignore_empty (required): This argument is basically used as a setting to handle empty cells in ranges. If this argument is set to TRUE, Excel TEXTJOIN function will ignore empty values. If it is FALSE, the function will include the empty values in the result string.
text1 (required): It is text value to be joined. It can be a string or text value or an array or range of cells.
[text2, ….] (optional): This argument contains additional text values to be joined.
In this example, we have information for real estate agents in multiple fields, like Name, State, Phone, Email, and we need to join these fields’ values in a single string, where each field’s value is separated by a delimiter “, “ in resulting string. Following formula will be used using Excel TEXTJOIN function;
=TEXTJOIN(", ",TRUE, A2:D2)
Image Tile: TEXTJOIN_function_1
Suppose we have information of various contracts, their start, and end dates, and we want to combine these values in a single cell, with each value separated by a delimiter “, “ in the resulting string using Excel TEXTJOIN function.
As date and time values are stored as the serial number in Excel and when we join them as text values, Excel returns these values as a serial number (text values), not in date and time formats.
Using Excel TEXTJOIN function, we can join date and time values with other text values in their formats using TEXT function within Excel TEXTJOIN function. Following formula will be used in this case;
=TEXTJOIN(", ",TRUE, A2:B2,TEXT(C2, "mm/dd/yyyy"),TEXT(D2,"mm/dd/yyyy"))
Image Title: TEXTJOIN_function_2