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:
- 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.
- 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.
- 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:
Syntax: =TEXTJOIN(“,”;TRUE;A2:C2)
Result:
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:
- #VALUE! error – Excel will return this error when the resulting string exceeds the cell limit, which is 32767 characters.
- The function can handle 252 text arguments.
- This function is quite similar to the CONCAT function. The difference between the two is that while TEXTJOIN accepts a delimiter, CONCAT doesn’t.
- #NAME? error – Occurs when you use an older version of Excel that doesn’t support this function.
- #NULL! error – Occurs when we forget to put the comma between the strings we wish to combine.
- It gives the user the flexibility to use cell ranges rather than individual cell references.
