Excel TEXTJOIN Function
Summary
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.
Purpose
Join text values with a delimiter
Return value
Result of concatenated text
Syntax
=TEXTJOIN (delimiter, ignore_empty, text1, [text2], ...)
Arguments
- delimiter - Separator between each text.
- ignore_empty - Whether to ignore empty cells or not.
- text1 - First text value or range.
- text2 - [optional] Second text value or range.
Usage notes
The TEXTJOIN function joins values from one or more text strings or ranges that contain text.
- Unlike the CONCATENATE function, TEXTJOIN allows you to specify a range of cells and a delimiter to use when joining text values.
- The second argument, ignore_empty, controls behavior for empty cells and text values. If set TRUE, empty values are skipped so that the delimiter is not repeated in the final result.
- TEXTJOIN is a new function in Excel 2016.
TEXTJOIN formula examples
You can reverse a text string with the TEXTJOIN and MID functions, by using an array constant. In the example shown, the formula in C5 is:
=TEXTJOIN("",1,MID(B5,{10,9,8,7,6,5,4,3,2,1},1))
How this formula...
To lookup and retrieve multiple matches in a comma separated list (in a single cell) you can use the IF function with the TEXTJOIN function. In the example shown, the formula in F5 is:
{=TEXTJOIN(", ",TRUE,...
To translate letters in a string to numbers, you can use an array formula based on the TEXTJOIN and VLOOKUP functions, with a defined translation table to provide the necessary lookups. In the example shown, the formula...
To abbreviate text that contains capital letters, you can try this array formula based on the TEXTJOIN function, which is new in Excel 2016. You can use this approach to create initials from names, or to create acronyms...
To join multiple cell values with a comma, you can use a formula based on the SUBSTITUTE and TRIM functions. You can use this same approach to concatenate values in cells with any delimiter you like. In the example...
To list holidays that occur between two dates, you can use a formula based on the TEXTJOIN and IF functions.
In the example shown, the formula in F8 is:
{=TEXTJOIN(", ",TRUE,IF(B4:B12>=F5,IF(B4:B12<=...
To remove numeric characters from a text string, you can try this experimental formula based on the TEXTJOIN function, new in Excel 2016. In the example shown, the formula in C5 is:
=TEXTJOIN("",TRUE,IF(...
To remove non-numeric characters from a text string, you can try this experimental formula based on the TEXTJOIN function, new in Excel 2016. In the example shown, the formula in C5 is:
{=TEXTJOIN("",TRUE,...
Related functions
Good links
