ID EN
…
#Text

TEXTJOIN

Excel Functions

Join text values with a delimiter

Syntax

EXCEL 
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

Arguments

Parameter Description
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.

Return Value

Concatenated text

Details

The TEXTJOIN function concatenates multiple values together with or without a delimiter. TEXTJOIN can concatenate values provided as cell references, ranges, or constants, and can optionally ignore empty cells. The TEXTJOIN function takes three required arguments: delimiter, ignore_empty, and text1. Delimiter is the text to use between values that are concatenated together and should be enclosed in double-quotes (""), for example, a space (" ") or a comma with a space (", "). To use no delimiter, supply an empty string (""). Ignore_empty is a Boolean (TRUE/FALSE) value that controls whether empty values should be ignored or added to the result. This is often set to TRUE to avoid delimiters with no content in the result from TEXTJOIN. Text1 is the first value to join together. This can be a

Examples

Example 1

Values are concatenated in the order they appear. With "Hello" in A1 and "World" in A2, the following formula returns "Hello World":

EXCEL 
=TEXTJOIN(" ",TRUE,A1,A2) // returns "Hello World"
Example 2

Changing the delimiter to a comma (", ") and reversing A1 and A2, we get "World, Hello":

EXCEL 
=TEXTJOIN(", ",TRUE,A2,A1) // returns "World, Hello"
Concatenating a range

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

EXCEL 
=TEXTJOIN(", ",TRUE,A1:A3)
Name with title

In the example below, TEXTJOIN is set up to concatenate names. Notice the cell reference for Title is provided first, followed by a range for First, M

EXCEL 
=TEXTJOIN(" ",1,E3,B3:D3)
Number formatting

When concatenating numbers, number formatting is lost. For example, with the date 1-Jul-2021 in cell A1, and 2-Jul-2021 in A2, the dates revert to ser

EXCEL 
=TEXTJOIN("-",1,A1,A2) // returns "44378-44379"
Number formatting

Use the TEXT function to apply formatting during concatenation:

EXCEL 
=TEXTJOIN("-",1,TEXT(A1,"mmm d"),TEXT(A2,"mmm d")) // "Jul 1-Jul 2"

See Also

CONCATENATE CONCAT