How to use TEXTJOIN function in Excel

The TEXTJOIN function joins text values from different ranges and/or strings together, using a delimiter that you define between each value. This function will effectively combine the ranges if the delimiter is an empty text string.

Multiple values can be concatenated using the TEXTJOIN function, either with or without a delimiter. Concatenating values from cell references, ranges, or constants is possible using TEXTJOIN, which also has the option to disregard empty cells. This article will be explaining different functions of TEXTJOIN in Excel.

TEXTJOIN expression: =TEXTJOIN(delimiter, ignore empty, text1, [text2],... text n)

The following parameters are used by the TEXTJOIN function:

Delimiter is the string that separates each text value in the output string (mandatory parameter). Commas and spaces are the two most widely used delimiters.

Ignore_empty (required argument): This option helps determine whether empty cells are included in the output string by specifying whether to ignore empty cells (mandatory argument). Empty values won't be taken into account if the argument is TRUE. If it returns FALSE, the results will also contain the empty values.

text1, text2,.... text n: The strings that we desire to link together are text1, text2,.... text n. We may combine up to 252 strings using the TEXTJOIN method.

Values are combined in the order that they are presented. The formula below yields Hello Mr. when Hello and Mr. are in A1 and B1, respectively:

Changing the delimiter to a comma (, ), we get:

Combining a Number Range:

Use TEXTJOIN as follows to link cells in the range A1:A3 with a comma and space:

=TEXTJOIN(, ,TRUE,A1:A3)

Check screenshot attached below:

The second option, ignore empty, regulates how empty cells and text values will be treated. Empty values are skipped if set to TRUE so that the delimiter is not used again in the outcome. TEXTJOIN will include empty values in the output if the value is set to FALSE.

Names with titles:

The TEXTJOIN function is configured to combine names in the example below. You'll see that the range for First, Middle, and Last is given after the cell reference for the Title. To prevent adding unnecessary space to names without Middle or Title values, Ignore Empty is set to 1 (TRUE). The equation in F3 is:

=TEXTJOIN ( ,1,E3,B3:D3)

Check screen shot attached below:

The formatting of numbers:

Number formatting is lost when numbers are concatenated. For instance, the dates become the serial numbers when the dates 1-Jan-2022 and 2-Jan-2022 are entered in cells A1 and A2, respectively:

Use the TEXT function to format during concatenation.

The outcome of the above formula is the text Jan 1-Jan 2.

Did you learn about how to use TEXTJOIN function in an Excel file? You can follow WPS Academy to learn more features of Word Document, Excel Spreadsheets and PowerPoint Slides.

You can also download WPS Office to edit the word documents, excel, PowerPoint for free of cost. Download now! And get an easy and enjoyable working experience