Knowledge base / Excel

Excel CONCATENATE: syntax, spaces and older workbooks

I trace every character in a label and explain why CONCATENATE has neither a delimiter option nor an empty-cell switch.

Kajo RudzińskiPublished Updated 4 min read

A press combines yellow blocks and individually supplied spacers into one bar

You find CONCATENATE in an older workbook and want to understand how a label is assembled. The function appends its arguments into one text string. It has no separate delimiter argument or option to ignore empty cells. Spaces and punctuation are ordinary pieces of text that you supply.

I start by listing the label’s components. That makes the positions of spaces, numbers and names explicit. The Microsoft documentation specifies CONCATENATE(text1, [text2], ...) and describes the function as retained for compatibility with older versions.

A small example with three cells

These values are synthetic. You can put headings in the first worksheet row and enter the values in the specified cells of the second row.

Cell Value
A2 Jan
B2 Adam
C2 Kowalski
=CONCATENATE(A2," ",B2," ",C2)

The result is Jan Adam Kowalski. The function joins five arguments: first name, a space, middle name, another space and surname. The quoted space becomes part of the output. The commas separating formula arguments do not become punctuation in the label.

Removing the two space arguments produces JanAdamKowalski. The function does not recognize names or infer word boundaries. Its predictable behavior is useful when every required character is explicitly described.

The ampersand operator offers a shorter form

You can produce the same result with:

=A2&" "&B2&" "&C2

Microsoft presents & as an alternative to CONCATENATE. For short labels, I often prefer this form because every component is easy to follow. Both versions require spaces to be added explicitly.

You can build an order label similarly. In a separate example, let A2 contain Ala and B2 contain the number 17. The formula =CONCATENATE("Customer: ",A2,", number: ",B2) returns Customer: Ala, number: 17. These cells now have different meanings from the name table. The output is a display string.

An empty middle name does not remove separators

Return to the name table and clear B2. The formula still contains two separate spaces, so two space characters remain between Jan and the surname. CONCATENATE has no ignore_empty argument that removes an unwanted separator.

Do not add TRUE as a second argument expecting to enable such a setting. Every argument is another item to concatenate. That differs from TEXTJOIN. Similar function names can easily lead to copying syntax intended for another function.

For a delimiter and omission of genuinely empty cells, use this in a supported Excel version:

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

With empty B2, the result is Jan Kowalski. The TEXTJOIN documentation describes its delimiter and empty-cell option. A cell containing a space is not an empty cell, however. Inspect the source before assuming blanks will disappear.

Two rows of blocks show explicit spacers and a missing middle piece

English name Polish name Main feature
CONCATENATE ZŁĄCZ.TEKSTY Older function joining arguments
CONCAT ZŁĄCZ.TEKST Also accepts ranges
TEXTJOIN POŁĄCZ.TEKSTY Delimiter and empty-cell option

For new work, I choose according to the task and the recipient’s Excel version. CONCAT replaces CONCATENATE, but also does not insert delimiters automatically. Microsoft specifies Office 2019 or a Microsoft 365 subscription for availability of the newer functions on Windows and Mac.

I do not assume every installation described merely as Excel 2016 supports those newer functions. Check its edition and updates. Maintaining CONCATENATE in an older workbook can be a compatibility decision. Rewriting a simple formula solely to change its name may provide little value.

Number formatting and practical limits

Combining a number with text does not automatically retain the number’s displayed format. Dates and percentages may need explicit formatting through TEXT. Microsoft explains this in its guide to combining text and numbers. Keep the original numeric field separately for further calculations.

The CONCATENATE documentation specifies at most 255 items. Functions accepting ranges are more convenient for combining larger collections. I still avoid building an entire data export into one cell. Separate columns retain structure that is easier to filter and inspect later.

Joining names also does not create a reliable person identifier. Two people may share a first and last name. A label helps readers, while a record key needs its own definition. No concatenation function resolves that identity problem.

Exercise: account for every character

Build the label from the three cells, then clear the middle name. Write the result while marking each space. Compare CONCATENATE, the ampersand operator and TEXTJOIN with empty cells ignored.

Next put one space in B2. Explain why this differs from an empty cell. Accounting for each output character makes it easier to repair addresses, product descriptions and order labels later. Explore related functions in the Excel knowledge section.

Key takeaways

Does CONCATENATE have a delimiter argument?

No. Supply each space or punctuation mark as text. Formula argument separators are not inserted into the result.

How can I ignore empty cells and add one space?

In a supported version, use TEXTJOIN with a space delimiter and TRUE. CONCATENATE has no such option.

How does CONCAT differ from CONCATENATE?

CONCAT is the newer replacement and accepts ranges. Neither function automatically inserts separators; TEXTJOIN provides that feature.

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.