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.
for example =TEXTJOIN(,TRUE,C11:C14,D11:D12)
Keep Reading Similar Functions:
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”.
REPT
The Excel REPT function repeat a text for a specified number of times. For example, =REPT(“M”,3) returns “MMM”.
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 ”.
CONCATENATE
The CONCATENATE function display text by merging multiple text strings. I.e., combine separate texts, =CONCATENATE(“Indian “, “Ocean”) returns “Indian Ocean”.