TEXTJOIN

Join multiple texts with a delimiter in Excel
Add multiple TEXT's with delimiter

Rational

Join multiple texts with a delimiter.

Description

The Excel TEXTJOIN function allows to concatenate (join) the text from multiple ranges and/or strings, and with a given delimiter between each text value.
For example, = TEXTJOIN(” ,”,TRUE,”Blue”,”Green”,”White”,”Black”) return ” Blue ,Green ,White ,Black”.

Syntax

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

Parameters

delimiter – Character to specify the boundary between each text value
ignore_empty – If TRUE, ignores empty cells. The default is FALSE
text – Strings to concatenate

Return Value

Return concatenated text.

Key Notes

  • The TEXTJOIN function also includes delimiter and ignore empty arguments, unlike the CONCAT function.
  • The ignore_empty must have TRUE argument to ignore empty cell otherwise the default is FALSE (return with blank cell).
  • We can use ‘&‘ operator to join different text strings but preferable for small strings.
  • And, the resulting text should not exceed 32767 characters (cell limit) otherwise it will return the #VALUE! error.
  • Introduced in Excel 2016 (Office 365), the TEXTJOIN function is a versatile version of CONCATENATE function.
Suppose that you want to join two columns text in a cell, for example here we have two columns color 1 and color 2. our task is to return all color name in a cell. 
for example =TEXTJOIN(,TRUE,C11:C14,D11:D12)

Keep Reading Similar Functions:

Repeat same text in Excel

REPT

The Excel REPT function repeat a text for a specified number of times. For example, =REPT(“M”,3) returns “MMM”.

Read More »
Merge series of multiple ranges and/or text strings in Excel

CONCAT

The CONCAT function display text by combining multiple ranges, array and/or strings. I.e., =CONCAT(“Mike “, “Tyson”) returns “Mike Tyson”, note space after “Mike ”.

Read More »
Merge multiple text strings in Excel in Excel

CONCATENATE

The CONCATENATE function display text by merging multiple text strings. I.e., combine separate texts, =CONCATENATE(“Indian “, “Ocean”) returns “Indian Ocean”.

Read More »
Substitutes new text for old text in a text string in Excel

SUBSTITUTE

The SUBSTITUTE function replace the specific text in a text string that occurs in a specific location. i.e, =SUBSTITUTE(“Butter”,”t”,”l”,2) return “Butler”.

Read More »