Knowledge base / Excel

TEXTJOIN in Excel: delimiters, empty cells and ranges

I build a readable label and a person list, showing why whole ranges do not automatically create row-wise pairs and how empty cells affect delimiters.

Kajo RudzińskiPublished Updated 6 min read

A mechanical clamp joins golden segments with spacers while skipping an empty tray

You have a first name, surname, and optional department in three columns. You want one readable label without repeated delimiters when the department is empty. I use TEXTJOIN for that task. First, though, I decide which cells should form one output item.

The correct name and Excel version

The official Polish name for TEXTJOIN is POŁĄCZ.TEKSTY. Use POŁĄCZ.TEKSTY in Polish formulas; TEKST.ZŁĄCZ is not a valid name for the function.

Microsoft specifies Office 2019 or a Microsoft 365 subscription, and also lists Excel 2021 and 2024. I do not describe it as available in every perpetual Excel 2016 installation. When preparing a workbook for another person, establish their software version before relying on the function.

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

The first argument is the delimiter and the second controls empty-cell handling. Text values or ranges follow. These examples use English names, TRUE, and commas; the Polish version uses local names and semicolons. The argument separator depends on regional settings, while a separator inside quotation marks belongs to the text being produced.

One label from one row

Use the following synthetic data, with headings in row 1. C2 is genuinely empty. The word “empty” below is an explanation and should not be entered into the workbook.

Row A: first name B: surname C: department
2 Jan Kowalski empty
3 Anna Nowak Marketing
=TEXTJOIN(" ",TRUE,A2:C2)

The result is Jan Kowalski. Copying the formula to the next row produces Anna Nowak Marketing. The delimiter is a space, and the empty cell does not require an extra space at the end of the first label.

If the requirement is just a full name, select A2:B2. The range belongs to the requirement. Including a department, identifier, or notes column changes the label even if the formula still looks compact. I do not select an entire row simply because it is convenient to highlight.

A comma differs from a space

=TEXTJOIN(", ",TRUE,A2:C2)

The first row produces Jan, Kowalski, while the second produces Anna, Nowak, Marketing. The comma also appears between the first name and surname because the delimiter separates each consecutive pair of joined values. TEXTJOIN does not interpret a first name and surname as one person; it sees separate cell values.

That is a reason to build a label in stages. If the first name and surname should form one component, combine them with a space first. Then add the department with another delimiter. The composition rules remain visible instead of depending on an unstated interpretation of the columns.

Empty cells and apparently empty data

TRUE skips empty cells. With FALSE, a delimiter can remain for an empty field, leaving the first row with an additional comma and space at the end. The appropriate choice depends on the output: a readable label usually benefits from omission, while a fixed field layout may need to preserve positions.

A cell containing a space is not an empty cell. These examples also do not automatically equate a genuinely empty field with every formula output that looks blank. When a source has several representations of missing data, establish a preparation rule first. Appearance alone does not identify a cell’s contents.

Removing a delimiter does not repair missing information. A label without a department may be readable while the department remains unknown. If that field is required, I keep a separate quality indicator instead of hiding the issue inside a tidier string.

Two ranges do not automatically create pairs

=TEXTJOIN(", ",TRUE,A2:A3,B2:B3)

This returns Jan, Anna, Kowalski, Nowak. The first-name range is supplied before the surname range. It does not create Jan Kowalski, Anna Nowak. The function does not infer that adjacent columns should first be paired by row.

For a simple workbook, I use two stages. In D2, I enter =TEXTJOIN(" ",TRUE,A2:B2) and copy it to D3, producing two complete labels. Then I assemble a list:

=TEXTJOIN("; ",TRUE,D2:D3)

The result is Jan Kowalski; Anna Nowak. The helper column exposes what constitutes one item and makes a missing surname easier to spot. With two records, I do not need to complicate the formula with arrays. Preparing an item and assembling a list are separate steps.

The upper path combines entire ranges while the lower path first assembles corresponding pairs

Joining text does not preserve every cell format

Adding a date or percentage to a label may not preserve its cell display format. Microsoft describes TEXT as a way to specify formatting when combining values. The source cell’s appearance and the contents of the final string are separate matters.

I do not present one format code as universal across language settings. For another recipient, agree the date and number notation before building the label. The text 01/02 can be ambiguous even when it originated from a valid date value.

I also retain the original fields when they support further analysis. Combining everything into one cell is useful for presentation but makes later filtering by separate attributes harder. A label can supplement a structured table without replacing its individual columns.

Limits and simpler alternatives

The documentation specifies up to 252 text arguments, each of which can be a range. A result longer than 32767 characters causes #VALUE!, displayed as #ARG! in Polish Excel. TEXTJOIN is therefore not a way to place an arbitrarily large table inside one cell.

If the function is unrecognised, check its name, the software version, and argument separators. Confusing the Polish names TEKST.ZŁĄCZ and POŁĄCZ.TEKSTY is different from exceeding the result-length limit. Reducing the range does not repair an incorrect function name.

For two required fields, the & operator may be sufficient: =A2&" "&B2. The older CONCATENATE function is called ZŁĄCZ.TEKSTY in Polish, not SCAL.TEKSTY. Those alternatives do not automatically provide the delimiter and empty-cell handling used in the TEXTJOIN example.

Exercise: prepare an item before a list

Create a row with a first name, surname, and empty department. Assemble the full name first, then combine it with the department using a comma and space. Explain why you do not want a trailing comma for the empty department. Fill in the department and predict the next result before reading the formula output.

Then create a two-person list using the helper-column approach. Compare it with joining A2:A3 and B2:B3 directly. Explaining the difference in order shows that you can deliberately define one item’s boundaries. For preparing earlier text fragments, see MID and SEARCH.

Key takeaways

What is TEXTJOIN called in Polish Excel?

POŁĄCZ.TEKSTY. TEKST.ZŁĄCZ is not a valid function name.

Do two ranges automatically pair names by row?

No. In the A2:A3, B2:B3 example, first names are combined before surnames. Prepare full names by row first, then join those labels.

Is TEXTJOIN available in perpetual Excel 2016?

Microsoft specifies Office 2019 or Microsoft 365 and lists newer editions. Do not assume availability in every Excel 2016 installation.

AI was used in an automated way while working on this article. If you want to learn how to do it too, check out this project.

Kajo Rudziński

Newsletter KajoData

Let’s keep in touch.

I write about data analysis, learning and changing careers. My newsletter and the sign-up form are in Polish. If that works for you, I’d be glad to see you there.