Excel CONCATENATE Function: Syntax, Examples and Tips

Part of the free Module 5: Excel Formulas and Functions · Function 15 of 102 · Full Excel course

The Excel CONCATENATE function joins up to 255 text strings, numbers or cell references into a single text string. It is a compatibility function: Excel 2019 and Microsoft 365 replaced it with CONCAT and TEXTJOIN, but CONCATENATE still works in every version, which makes it the safe choice for workbooks shared with users of Excel 2016 or earlier.

Syntax

=CONCATENATE(text1, [text2], ...)

Arguments

Argument Required / Optional Meaning
text1 Required The first value to join: text in quotes, a number or a single cell reference.
text2, … Optional Up to 254 more values. Each must be a single value; a range such as A2:A5 is not accepted.

How CONCATENATE works

Each argument is converted to text and appended in order. No separator is added, so spaces, commas or hyphens must be supplied as their own arguments. Numbers are joined as stored, which means a date is joined as a serial number unless you format it with TEXT. The result cannot exceed 32,767 characters. The & operator does exactly the same job with less typing: =A2&" "&B2 equals =CONCATENATE(A2," ",B2).

Step-by-step example: full address in one cell

  1. Enter street, city and postcode in columns A, B and C.
  2. In D2 type =CONCATENATE(A2,", ",B2," ",C2).
  3. Press Enter. The result reads like 12 Park Road, Leeds LS1 4AB.
  4. Fill the formula down the column. If any postcode is missing, add IF to avoid a trailing space: =CONCATENATE(A2,", ",B2,IF(C2="",""," "&C2)).

Practical use cases

1. Text plus a calculated total

=CONCATENATE("Total sales: ",TEXT(SUM(B2:B10),"#,##0"))

Builds a report caption that updates automatically. TEXT keeps the thousands separator.

2. Join a date correctly

=CONCATENATE("Report date: ",TEXT(TODAY(),"dd mmm yyyy"))

Without TEXT the formula would display a number such as 46265.

3. Create a lookup key

=CONCATENATE(A2,"|",B2)

Combining two columns into one key lets VLOOKUP match on two criteria.

4. Add a line break between parts

=CONCATENATE(A2,CHAR(10),B2)

Enable Wrap Text to see the second line.

Common mistakes and tips

  • #VALUE! with a range: =CONCATENATE(A2:A5) does not work. Use CONCAT or TEXTJOIN, or list the cells individually.
  • Missing spaces: remember to add " " between words.
  • Numbers lose formatting: wrap currency, percentages and dates in TEXT.
  • Empty cells leave double separators: TEXTJOIN with ignore_empty set to TRUE solves this in one step.
  • Modern alternative: in new workbooks use CONCAT or TEXTJOIN; Microsoft has marked CONCATENATE as a compatibility function and it may be removed in the future.

Migrating from CONCATENATE to newer functions

If you maintain older workbooks, you do not need to replace every CONCATENATE formula; they will keep working. Replacing them is worthwhile when a formula lists many cells one by one or produces double separators because of blanks. For example, =CONCATENATE(A2,", ",B2,", ",C2,", ",D2) becomes =TEXTJOIN(", ",TRUE,A2:D2), which is shorter, skips empty cells and is easier to extend when a column is added. When a formula joins just two values, the ampersand operator is the simplest replacement. Whichever function you use, the rule for dates and numbers is the same: format them with TEXT before joining, because every joining function converts a date to its serial number. Use Find and Replace with “CONCATENATE(” as the search text to locate every instance in a workbook quickly.

Related functions

  • CONCAT: the replacement that also accepts ranges.
  • TEXTJOIN: joins with a delimiter and can ignore blanks.
  • TEXT: formats numbers and dates before joining.
  • LEN: checks the length of the combined string.
  • See all lessons in the Excel Formulas course.

Frequently asked questions

Is CONCATENATE still available in Excel?

Yes. It works in all versions including Microsoft 365, but Microsoft lists it as a compatibility function and recommends CONCAT or TEXTJOIN for new work.

Can CONCATENATE join a range of cells?

No. Each argument must be a single value. Use CONCAT(A2:A10) or TEXTJOIN(“, “,TRUE,A2:A10) to join a range.

How do I add a space between words with CONCATENATE?

Include a text space as its own argument, for example =CONCATENATE(A2,” “,B2).

Need a ready-made template? Browse 7,000+ Excel, Power BI and Google Sheets templates at NextGenTemplates.com.

← PreviousExcel CONCAT Function: Syntax, Examples and TipsAll functionsExcel Formulas and FunctionsNext →Excel COUNTIF Function: Syntax, Examples and Tips