Exceljet

Quick, clean, and to the point

Excel TEXTJOIN Function

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

TEXTJOIN is a new function, available in Office 365 and Excel 2019.

The TEXTJOIN function concatenates values from one or more text strings or ranges that contain text. Unlike the CONCATENATE function, TEXTJOIN lets you specify a range of cells and a delimiter to use when joining text values.

Example #1

To join cells in the range A1:A3 with a comma and space, you can use TEXTJOIN like this:

=TEXTJOIN(", ",TRUE,A1:A3)

 TEXTJOIN basic example

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. If set to FALSE, TEXTJOIN will include empty values in the output.

Example #2

To join cells in the range A1:A3 with no delimiter, including empty values, you can use:

=TEXTJOIN("",FALSE,A1:A3)

Notes

  • To concatenate manually, use the concatenation operator (&)
  • The CONCAT function also provides basic concatenation, but provides no options for delimiters or empty values.
  • Numbers provided to TEXTJOIN will be converted to text values during concatenation.
See also 

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.