CONCAT

How CONCAT works

CONCAT walks through every value you give it (cells, ranges, or literal strings) and sticks them end to end with nothing between them. No delimiter, no spacing, no skip-blanks switch. Whatever you pass in is what you get back, glued together in order.

The big upgrade over CONCATENATE is range support. CONCAT(A2:A6) joins five cells in one shot. CONCATENATE could not do this - you had to type out CONCATENATE(A2, A3, A4, A5, A6) cell by cell, which got painful fast. CONCAT also stays useful as a replacement for the & operator when you have more than two or three pieces to combine.

Because CONCAT has no built-in delimiter, you supply spaces, commas, or punctuation by typing them as literal strings between the references. =CONCAT(A2, " ", B2) puts a space between first and last name. =CONCAT(A2, ", ", B2) puts a comma-space. If you find yourself typing the same separator over and over, switch to TEXTJOIN.

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

Arguments

Combine first name + last name into a full name

The classic CONCAT case. First name in column A, last name in column B, full name produced in column C. The trick is the literal " " between them - without it the two names slam together with no space.

This is a per-row formula. Write =CONCAT(A2, " ", B2) in C2, grab the fill handle, drag down through C9. Each row joins its own first and last name independently.

=CONCAT(A2, " ", B2)

• •A2 -> the first-name cell on this row (blue)
• •" " -> a literal space between the two names - if you forget it, the result is 'SarahChen'
• •B2 -> the last-name cell on this row (purple)
• •Per-row formula -> write it once in C2 and drag down through C9 to fill the column
• •Result -> "Sarah Chen" lands in C2, "James Miller" in C3, and so on down the column

CONCAT a whole range in one shot

Here is what CONCAT does that CONCATENATE never could: accept a range as a single argument. =CONCAT(A2:E2) joins five cells in one shot, no commas between the references, no painful retyping.

Below, each row has address parts spread across columns A through E (street, city, state, zip, country). CONCAT(A2:E2) glues them together. The catch is that CONCAT has no delimiter, so you either pre-format your cells with trailing punctuation, or you accept the no-space result and clean it up later (or just use TEXTJOIN, which was built for this).

=CONCAT(A2:E2)

• •A2:E2 -> a five-cell range on this row - CONCAT joins them in order, left to right
• •No delimiter -> CONCAT does not insert anything between cells, so include trailing spaces or commas in the source data if you want them
• •CONCATENATE could not do this -> the older function rejected ranges - you had to write CONCATENATE(A2, B2, C2, D2, E2) one cell at a time
• •Per-row formula -> drag from F2 down through F4 to fill the column
• •Better fit for TEXTJOIN -> if you want clean commas and skip-blanks behavior, TEXTJOIN(", ", TRUE, A2:E2) is the cleaner choice

CONCAT vs the & operator: same result, different syntax

The & operator does exactly what CONCAT does for two or three cells, and a lot of veterans reach for & before CONCAT out of habit. Below, columns C and D produce identical full names from the same source - one uses CONCAT, the other uses &.

Pick whichever reads better in context. For two or three pieces, & is shorter and easier on the eyes: A2 & " " & B2. For five or more pieces, or when you have a whole range to join, CONCAT wins because you can pass A2:F2 in one go.

=CONCAT(A2, " ", B2)   vs   =A2 & " " & B2

• •CONCAT version -> =CONCAT(A2, " ", B2) - more verbose but reads like a function call
• •& version -> =A2 & " " & B2 - operator-style, shorter for two or three items
• •Identical output -> both produce 'Sarah Chen', byte-for-byte the same
• •When to use & -> two or three pieces, you want the formula to stay short
• •When to use CONCAT -> many pieces, or you want to pass a whole range like CONCAT(A2:F2)

Build standardized emails from first + last names

Onboarding 50 new hires and IT needs an email list in the firstname.lastname@acme.co format. The pattern is CONCAT + LOWER, in one nested formula, dragged down the column.

LOWER forces consistent casing even when the source data is messy - typing 'SARAH' or 'sarah' or 'Sarah' all collapse to 'sarah' on output. This kind of normalization in the formula itself is faster than asking HR to fix their export.

=CONCAT(LOWER(A2), ".", LOWER(B2), "@acme.co")

• •LOWER(A2) -> force lowercase on the first name, so 'Sarah' becomes 'sarah'
• •"." -> the literal dot separator between first and last name
• •LOWER(B2) -> force lowercase on the last name, even if HR's export is inconsistent
• •"@acme.co" -> the literal domain suffix
• •Per-row formula -> write it once in C2, drag down through C9 - each row produces its own email
• •Result -> "sarah.chen@acme.co" in C2, "james.miller@acme.co" in C3, and so on

When CONCAT gets ugly: switch to TEXTJOIN

CONCAT works for the simple cases. The moment you start typing the same delimiter five times in a row, you have outgrown it.

Below, the same five-name roster is joined into one comma-separated string two ways. The CONCAT version is awkward and easy to break. The TEXTJOIN version is one clean call. Same output, but TEXTJOIN also skips blanks automatically.

=CONCAT(A2, ", ", A3, ", ", A4, ", ", A5, ", ", A6)   vs   =TEXTJOIN(", ", TRUE, A2:A6)

• •CONCAT version -> five cell references and four literal delimiters, hand-typed - one typo and the formula breaks
• •TEXTJOIN version -> one delimiter, one range, one boolean - reads in a glance
• •Same output -> both produce 'Sarah, James, Maria, David, Alex'
• •TEXTJOIN bonus -> the TRUE argument skips blanks, so an empty cell in the middle doesn't create a double comma
• •Rule of thumb -> more than three cells with the same delimiter? Switch to TEXTJOIN

Where people go wrong

1. 01
   Forgetting the delimiter between cells

   CONCAT does not insert anything between values. =CONCAT(A2, B2) joins 'Sarah' and 'Chen' into 'SarahChen' with no space. Beginners run into this within the first ten seconds of using the function.

   Fix: Include the separator as a literal string between the references: =CONCAT(A2, " ", B2) for a space, =CONCAT(A2, ", ", B2) for a comma-space, =CONCAT(A2, "-", B2) for a hyphen. Whatever character you want in the result, you have to type.
2. 02
   Reaching for CONCATENATE out of habit

   CONCATENATE still works in modern Excel for backward compatibility, and a lot of older spreadsheets and tutorials use it. But it does not accept ranges - CONCATENATE(A2:A6) errors out. Anything CONCATENATE does, CONCAT does better.

   Fix: Default to CONCAT (or & for two-three pieces, or TEXTJOIN when you need a delimiter and skip-blanks). Only stay on CONCATENATE if you have to support Excel 2016 or earlier where CONCAT does not exist.
3. 03
   Long CONCAT chains that should be TEXTJOIN

   =CONCAT(A2, ", ", B2, ", ", C2, ", ", D2, ", ", E2) is technically valid but painful to read, painful to edit, and breaks the moment you add a sixth column. Every comma-space is hand-typed and easy to forget on one of the joins.

   Fix: Switch to =TEXTJOIN(", ", TRUE, A2:E2). One delimiter argument, one range, automatic skip-blanks. Reads better, scales as you add columns, and handles missing cells without leaving double commas.
4. 04
   Treating the CONCAT result as a number

   CONCAT always returns text. Even when every input is a number, the joined output is a string. =CONCAT(A2, B2) where A2 is 100 and B2 is 50 returns the text "10050", not the number 10050. Try to do math on it and you get #VALUE! errors.

   Fix: If you need the result as a number, wrap with VALUE: =VALUE(CONCAT(A2, B2)). If you genuinely want to add the two numbers (not concatenate), use + instead: =A2 + B2. CONCAT is for text output only.

Notes

• CONCAT was added in Excel 2019. Available in Excel 2019, Excel 2021, Excel 365, and Excel for the web. Excel 2016 and earlier do not have it - fall back to CONCATENATE or & on those versions.
• CONCATENATE (the older sibling) is still supported in modern Excel for backward compatibility. It works fine for two or three cells but cannot accept a range.
• The & operator does the same thing for two or three strings: =A2 & " " & B2. More readable when you only have a few pieces to combine.
• CONCAT accepts ranges: =CONCAT(A2:A6) joins five cells with no delimiter. CONCATENATE could not do this - one of the main reasons CONCAT replaced it.
• The result is always text, never a number, even if every input is a number. Wrap with VALUE() if you need to convert the joined string back to a number.
• Empty cells in a range are skipped silently. No double-delimiter problem like the old CONCATENATE+& chains had, because there is no delimiter to double in the first place.
• Maximum result length is 32,767 characters (the standard Excel cell limit). Hitting this in normal work is rare; usually you have other problems if you are joining that much text.
• For delimiter support (commas, semicolons, line breaks between items) or auto-skip-blanks behavior, use TEXTJOIN instead. CONCAT is for raw glue-the-strings-together; TEXTJOIN is for formatted lists.

What to learn next

Now prove it