Share on twitter
Share on linkedin
Share on facebook
Share on pinterest
Share on email
Share on print
Share on twitter
Share on linkedin
Share on facebook
Share on pinterest
Share on email
Share on print

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:

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 »
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 »