TEXTJOIN in Excel: join text from many cells with a delimiter

TEXTJOIN syntax and arguments

=TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)

delimiter is the text placed between the items: ", " for a comma and a space, " | " for a pipe, "" for nothing at all. It can also be a cell reference, so the separator can be changed in one place. ignore_empty is TRUE to skip empty cells and empty strings, FALSE to keep them, which produces two delimiters in a row. text1, text2, … are the items: single cells, ranges, text in quotes, numbers or the result of another formula, up to 252 arguments. A range is read row by row, left to right, then down.

The result is always text. If it would be longer than 32,767 characters, the limit of a cell, TEXTJOIN returns #VALUE!.

&, CONCAT or TEXTJOIN: which one to use

For two or three pieces the ampersand is the quickest: =A2&" "&B2 returns "Anna Keller". It is available in every version, but each separator has to be typed again between every pair of cells. CONCAT accepts whole ranges but adds no separator, so =CONCAT(A2:A6) returns "AnnaMarcoLeaTomSara". The older CONCATENATE only takes single cells and is kept for compatibility.

TEXTJOIN is the right choice as soon as you need a separator between many items, want to join a range, or have cells that may be empty. A rule of thumb: & for building one label from a few cells, TEXTJOIN for turning a column into a list.

Step by step: labels with numbers and dates

Numbers lose their cell formatting when they are joined, because the formula uses the stored value. Hours stored as 40 would appear as "40", and 32.5 formatted to show 32.50 still appears as "32.5". Wrap numbers in TEXT with a format code: =TEXTJOIN(" | ",TRUE,B2,C2,TEXT(D2,"0.0")&" h") gives Keller | Design | 32.5 h, and for Marco "Rossi | Sales | 40.0 h". The same applies to sums inside a sentence: ="Total: "&TEXT(SUM(D2:D6),"0.0")&" h" returns "Total: 158.0 h".

Dates are the classic trap. A date is stored as a serial number, so joining it directly shows something like 46325. Use TEXT(A2,"dd.mm.yyyy") or TEXT(A2,"yyyy-mm-dd") inside TEXTJOIN. For amounts, TEXT(B2,"#,##0.00") keeps the thousands separator and two decimals.

Worked example: names, labels, filtered lists and addresses

The staff list and the address parts are inputs (yellow); B18 is deliberately empty. Columns E and F are filled down, the middle block joins the whole list in one cell each.

Worked example: names, labels, filtered lists and addresses

	A	B	C	D	E	F
1	First name	Last name	Team	Hours	Full name (&)	Label (TEXTJOIN)
2	Anna	Keller	Design	32.5	Anna Keller	Keller | Design | 32.5 h
3	Marco	Rossi	Sales	40.0	Marco Rossi	Rossi | Sales | 40.0 h
4	Lea	Brun	Design	18.0	Lea Brun	Brun | Design | 18.0 h
5	Tom	Weber	Support	40.0	Tom Weber	Weber | Support | 40.0 h
6	Sara	Meier	Sales	27.5	Sara Meier	Meier | Sales | 27.5 h
7
8	Joined from the whole list			Result
9	All first names, comma-separated			Anna, Marco, Lea, Tom, Sara
10	Design team only (TEXTJOIN + IF)			Anna, Lea
11	More than 30 hours (TEXTJOIN + FILTER)			Anna, Marco, Tom
12	Each team once (TEXTJOIN + UNIQUE)			Design, Sales, Support
13	Last names A to Z (TEXTJOIN + SORT)			Brun, Keller, Meier, Rossi, Weber
14	First names with CONCAT (no delimiter)			AnnaMarcoLeaTomSara
15	Total hours as text with TEXT			Total: 158.0 h
16
17	Street	Address line 2	ZIP	City	ignore_empty TRUE	ignore_empty FALSE
18	Bahnhofstrasse 12		4051	Basel	Bahnhofstrasse 12, 4051, Basel	Bahnhofstrasse 12, , 4051, Basel
19	Rue du Lac 3	2nd floor	1003	Lausanne	Rue du Lac 3, 2nd floor, 1003, Lausanne	Rue du Lac 3, 2nd floor, 1003, Lausanne

Fictional names and addresses. TEXTJOIN needs Excel 2019, Excel 2021, Microsoft 365 or Excel for the web; FILTER, UNIQUE and SORT need Excel 2021 or Microsoft 365.

Join only the rows that match: TEXTJOIN with IF or FILTER

To list only the Design team, let IF return the name for matching rows and an empty string for all others, and let TEXTJOIN skip the empty strings:

=TEXTJOIN(", ",TRUE,IF(C2:C6="Design",A2:A6,"")) returns Anna, Lea.

In Microsoft 365 the formula works as it is; in Excel 2019 confirm it with Ctrl+Shift+Enter as an array formula. With FILTER the condition reads more naturally: =TEXTJOIN(", ",TRUE,FILTER(A2:A6,D2:D6>30)) lists everyone with more than 30 hours, "Anna, Marco, Tom". If no row matches, FILTER returns #CALC!; give it a third argument such as FILTER(A2:A6,D2:D6>50,"") to get an empty result instead. Wrapping UNIQUE or SORT inside TEXTJOIN gives a distinct list ("Design, Sales, Support") or an alphabetical one ("Brun, Keller, Meier, Rossi, Weber"). More about distinct lists is in the UNIQUE function guide.

Blank cells: ignore_empty TRUE vs FALSE

The address block shows why the second argument matters. The first address has no second line. With TRUE the result is Bahnhofstrasse 12, 4051, Basel; with FALSE it is "Bahnhofstrasse 12, , 4051, Basel", with an empty slot between two commas. For the second address both versions are identical, because all four parts are filled.

FALSE is useful when the position of each item carries meaning, for example when you build a delimited line for an import file and every field must stay in its column. Note that a cell containing a single space is not empty: TEXTJOIN keeps it even with TRUE. Clean such data with TRIM first, or use a condition like IF(TRIM(A2:D2)="","",A2:D2).

Line breaks, and splitting the text again

To put each item on its own line inside one cell, use CHAR(10) as the delimiter and turn on Wrap Text for the cell: =TEXTJOIN(CHAR(10),TRUE,A18:D18). To reverse a join in Microsoft 365, TEXTSPLIT splits the text at a delimiter into separate cells. In other versions, extracting single parts needs FIND and MID, described in extract text between two characters.

Common mistakes

• Forgetting the quotes around the delimiter. =TEXTJOIN(,,TRUE,A2:A6) is a syntax error; the delimiter is text and needs quotes, for example ", ".
• Joining numbers and dates without TEXT. Dates turn into serial numbers and decimals lose their formatting. Wrap them in TEXT with a format code.
• Using FALSE by accident. With ignore_empty FALSE every empty cell leaves two delimiters in a row, which looks like a typo in the result.
• "Empty" cells that contain a space. TEXTJOIN does not skip them. Clean the data with TRIM or test for blank after trimming.
• #NAME? in older Excel. TEXTJOIN does not exist in Excel 2016 and earlier. The file shows #NAME? there; use & or CONCATENATE for compatibility.
• Expecting the result to update the source. TEXTJOIN returns text. If you need the parts again, split them with TEXTSPLIT instead of retyping.