Skip to content

Excel function · Text

Excel TEXTJOIN function

TEXTJOIN joins the values of a range into one text with the delimiter you choose between them, skipping empty cells if you want. It is ideal for listing the reps of a region or the items of an order in one cell.

In Turkish Excel: METİNBİRLEŞTİR

What it does

The first argument of TEXTJOIN is the delimiter: ", ", " - ", ";" or CHAR(10) for a line break. The second decides what happens to empty cells: TRUE skips them, FALSE adds a delimiter for them too, so you see two delimiters side by side. Then come the cells or ranges to join.

Its real strength shows when it is combined with a condition. In Microsoft 365, =TEXTJOIN(", ",TRUE,FILTER(A2:A9,B2:B9="West")) joins only the names in the West region. Without FILTER, IF(B2:B9="West",A2:A9,"") does the same, because rows that fail the test become empty text and are skipped; in Excel 2019 that formula must be entered as an array formula with Ctrl+Shift+Enter.

TEXTJOIN is available from Excel 2019. To split the joined text into parts again, use TEXTSPLIT; to join without a delimiter, use CONCAT.

Syntax

English Excel=TEXTJOIN(delimiter, ignore_empty, text1, ...)
Turkish Excel=METİNBİRLEŞTİR(sınırlayıcı; boşları_yoksay; metin1; ...)

Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.

Arguments

ArgumentRequiredDescription
delimiterRequiredThe text placed between the values, e.g. ", ". With empty text "" the values are written next to each other.
ignore_emptyRequiredTRUE: empty cells and empty text are skipped. FALSE: a delimiter is added for them too.
text1RequiredThe cells, ranges or texts to join. Several ranges can be given; they are joined in order.

Examples

1Join a list and skip the blanks

One row of the attendee list is empty. The formula in C2 skips it; the one in D2 has FALSE as its second argument, so it adds a delimiter for the empty cell as well.

C2fx=TEXTJOIN(", ",TRUE,A2:A6)
Join a list and skip the blanks: example table
ABCD
1AttendeeBlanks skippedBlanks included
2EmmaEmma, Liam, Olivia, NoahEmma, Liam, , Olivia, Noah
3Liam
4
5Olivia
6Noah
  • C2=TEXTJOIN(", ",TRUE,A2:A6)
  • D2=TEXTJOIN(", ",FALSE,A2:A6)

C2 shows Emma, Liam, Olivia, Noah. In D2, two commas appear side by side where the empty row was: Emma, Liam, , Olivia, Noah.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

2List the reps of each region in one cell

The formula in E2 is filled down. IF returns the name where the region matches the one in column D and empty text elsewhere; TEXTJOIN skips the empty texts.

E2fx=TEXTJOIN(", ",TRUE,IF($B$2:$B$7=D2,$A$2:$A$7,""))
List the reps of each region in one cell: example table
ABCDE
1RepRegionRegionReps
2EmmaWestWestEmma, Olivia, Mia
3LiamNorthNorthLiam, Ava
4OliviaWestSouthNoah
5NoahSouth
6AvaNorth
7MiaWest
  • E2 (filled down to E4)=TEXTJOIN(", ",TRUE,IF($B$2:$B$7=D2,$A$2:$A$7,""))

The West row shows Emma, Olivia, Mia, the North row Liam, Ava and the South row Noah. In Microsoft 365 you can get the same result with FILTER.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

3Build an address line and skip empty parts

The address parts are in separate columns, and some companies have no second address line. Because blanks are skipped, no extra commas appear.

F2fx=TEXTJOIN(", ",TRUE,B2:E2)
Build an address line and skip empty parts: example table
ABCDEF
1CompanyAddress 1Address 2TownPostcodeAddress
2Brown & Sons12 Station RoadUnit 3LeedsLS1 4AB12 Station Road, Unit 3, Leeds, LS1 4AB
3Northgate Ltd45 High StreetYorkYO1 7HU45 High Street, York, YO1 7HU
4Harbour Trade8 Quay SideBlock BBristolBS1 5TR8 Quay Side, Block B, Bristol, BS1 5TR
5Star Market3 Market LaneHullHU1 2AB3 Market Lane, Hull, HU1 2AB
  • F2 (filled down to F5)=TEXTJOIN(", ",TRUE,B2:E2)

F2 shows 12 Station Road, Unit 3, Leeds, LS1 4AB; F3, which has no second address line, becomes 45 High Street, York, YO1 7HU.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

Common errors and fixes

#VALUE!

Cause: The joined text exceeds the 32,767-character limit.

Fix: Join a smaller range or split the result.

#CALC!

Cause: The FILTER inside found no matching rows and returned an empty-array error.

Fix: Give FILTER empty text as its third argument: FILTER(A2:A9,B2:B9="West","").

Delimiters side by side

Cause: ignore_empty was FALSE, so a delimiter was added for each empty cell: "Emma, , Liam".

Fix: Set the second argument to TRUE.

Good to know

  • To show the list one item per line, use CHAR(10) as the delimiter and turn on Wrap Text.
  • To join each value only once, combine it with UNIQUE in Microsoft 365: =TEXTJOIN(", ",TRUE,UNIQUE(B2:B20)).

In Nexell

Get Nexell to do it from a sentence

Type this in Nexell

Make a summary that lists the reps of each region in one cell, separated by commas

Convert to table

Bring your rep list into the tool and type this. Nexell builds a summary that gathers each region's reps into one cell and shows it in a preview first; once you approve, you download it as .xlsx.

Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.

Free account · no card needed · 30 credits a month

Frequently asked questions

How do I combine a column of values with commas in Excel?

Use =TEXTJOIN(", ",TRUE,A2:A20). Because the second argument is TRUE, empty cells in between are skipped.

How do I join only the values that meet a condition?

In Microsoft 365: =TEXTJOIN(", ",TRUE,FILTER(A2:A20,B2:B20="West")). Without FILTER, =TEXTJOIN(", ",TRUE,IF(B2:B20="West",A2:A20,"")) gives the same result.

Related functions

All text functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free