Excel TextJoin
This function becomes available in Excel 2019 and Office 365. For legacy systems, use CONCATENATE. See details here.
Usage
CONCAT concatenates a series of 1 or more values into a single string value with a string delimiter. The values can be string values, arrays of string values, or cell ranges.
=TEXTJOIN(DELIMITER, IGNORE_EMPTY, VALUE, [VALUE, ...])
DELIMITER is inserted between every string value. If set to "", TEXTJOIN effectively is the same as CONCAT.
If IGNORE_EMPTY is set to TRUE, empty cells will be skipped. This prevents duplicate delimiters from being inserted into the final string.
=TEXTJOIN(", ", TRUE, A2:A8)
Order of Cell Ranges
Given the following table:
|
A |
B |
1 |
a |
b |
2 |
c |
d |
The formula =TEXTJOIN(" ",TRUE,A:A,B:B) would return a c b d, while the formula =TEXTJOIN(" ",TRUE,A1:B2) would return a b c d.
Variable Delimiters
Given the following table:
|
A |
B |
C |
1 |
a |
b |
c |
2 |
d |
e |
f |
3 |
end |
|
|
4 |
, |
, |
; |
The formula =TEXTJOIN(A4:C4,TRUE,A1:C3) would return a,b,c;d,e,f;end.