How to use TEXTJOIN Function in Excel

The Excel TEXTJOIN function concatenates (joins) values with a given delimiter. Unlike the CONCAT function, TEXTJOIN allows you to supply a range of cells, and has a setting to ignore empty values.

Syntax:= TEXTJOIN (delimiter, ignore_empty, text1, [text2], …)

The TEXTJOIN function syntax has the following arguments:

  1. Delimiter (required argument) – The string that is inserted between each text value in the resulting string. The most common delimiters used are a comma or a space character.
  2. Ignore_empty (required argument) – This argument helps in determining whether empty cells are included in the resulting string. If the argument is TRUE, it will ignore empty values. If it is FALSE, it will include the empty values in the results.
  3. Text1, text2,….text_n – The strings that we wish to join together. The TEXTJOIN function allows us to join up to 252 strings together.

Example: Let’s look at some Excel TEXTJOIN function examples and explore how to use the TEXTJOIN function as a worksheet function in Microsoft Excel:

TEXTJOIN Function - How to use TEXTJOIN Function in Excel

Syntax:  =TEXTJOIN(“,”;TRUE;A2:C2)

Result:

TEXTJOIN Function in Excel - How to use TEXTJOIN Function in Excel

Based on the Excel spreadsheet above, the following TEXTJOIN examples would return:

Syntax: =TEXTJOIN(“/”;TRUE;A3:C3)
Result: Jack Albertson/Eddie Albert

Syntax: =TEXTJOIN(“”;TRUE;A4:C4)
Result: JackAlbertson

Syntax: =TEXTJOIN(“,”;TRUE;A5:C5)
Result: Potato,Apple,Banana

Syntax: =TEXTJOIN(“”;TRUE;A6:C6)
Result: PotatoAppleBanana

Syntax: =TEXTJOIN(“;”;FALSE;A7:C7)
Result: 1;2;3

Syntax: =TEXTJOIN({” “;”.”};FALSE;A8:C8)
Result: Eddie L.Albert

Syntax: =TEXTJOIN({” “;”.”};FALSE;A9:C9)
Result: Thomas G.Randell

Syntax: =TEXTJOIN({” “;”.”};TRUE;B10;LEFT(C10;1);A10)
Result: Andrew J.Brown

Syntax: =TEXTJOIN({” “;”.”};TRUE;B11;LEFT(C11;1);A11)
Result: Albertson Jack

Syntax: =TEXTJOIN({” “;”.”};TRUE;B12;LEFT(C12;1);A12)
Result: David M.White

Note:

  1. #VALUE! error – Excel will return this error when the resulting string exceeds the cell limit, which is 32767 characters.
  2. The function can handle 252 text arguments.
  3. This function is quite similar to the CONCAT function. The difference between the two is that while TEXTJOIN accepts a delimiter, CONCAT doesn’t.
  4. #NAME? error – Occurs when you use an older version of Excel that doesn’t support this function.
  5. #NULL! error – Occurs when we forget to put the comma between the strings we wish to combine.
  6. It gives the user the flexibility to use cell ranges rather than individual cell references.
READ:  How to use the Excel EXACT function

Add a Comment

Your email address will not be published. Required fields are marked *