CoreGPT
Microsoft 36510 min read

How to Concatenate in Excel: &, CONCAT and TEXTJOIN (With Examples)

Join text from several cells with the & operator, CONCATENATE, CONCAT or TEXTJOIN: add spaces, commas and line breaks, keep dates and currency formatted with TEXT, combine a whole column, and fix #NAME? and other common errors.

How to Concatenate in Excel: &, CONCAT and TEXTJOIN (With Examples)

To concatenate in Excel, type =A2&" "&B2 in an empty cell: the & operator joins the two cells, and " " adds the space between them. For more than two cells, =TEXTJOIN(" ",TRUE,A2:C2) joins a whole range with one separator and skips blank cells. =CONCAT(A2," ",B2) and the older =CONCATENATE(A2," ",B2) give the same result as &.

The main caveat is version support. & and CONCATENATE work in every version of Excel, but CONCAT and TEXTJOIN need Excel 2019 or later, Microsoft 365 or Excel for the web. In older versions, such as a standalone Excel 2016, they show a #NAME? error.

MethodExampleJoins a range?Separator built in?Skips blanks?Works in
& operator=A2&" "&B2NoNoNoEvery version
CONCATENATE=CONCATENATE(A2," ",B2)NoNoNoEvery version (legacy)
CONCAT=CONCAT(A2:C2)YesNoNoExcel 2019+, Microsoft 365, web
TEXTJOIN=TEXTJOIN(", ",TRUE,A2:C2)YesYesYesExcel 2019+, Microsoft 365, web
Flash FillCtrl+En/aAny patternn/aExcel 2013+ (no formula, no updates)

This post covers formulas that combine text into a new cell. If you want to turn several cells into one bigger cell for a title, that's a formatting job: see how to merge cells in Excel.

Combine two columns with the & operator

The ampersand is the quickest way to join a few cells, and it works in Excel for Windows, Mac, the web, mobile and Google Sheets.

Say first names are in column A and last names in column B:

  1. Click C2, the first empty cell in your new Full name column.
  2. Type =A2&" "&B2 and press Enter. "Emma" and "Johnson" become Emma Johnson.
  3. Double-click the fill handle (the small square at the bottom-right of C2) to copy the formula down the column.

Everything inside double quotes is added as typed, so you can mix in any text you like:

  • =B2&", "&A2 returns Johnson, Emma
  • ="Dear "&A2&"," returns Dear Emma,
  • =LEFT(A2,1)&". "&B2 returns E. Johnson

In Excel for Microsoft 365 and Excel 2021 or later you can also join two whole columns in one go: =A2:A7&" "&B2:B7 spills a full name for every row, with no copying down.

CONCATENATE vs CONCAT: which should you use?

CONCATENATE is the original function: =CONCATENATE(A2," ",B2). It still works in every version, but it only accepts single cells or text, so joining ten cells means listing all ten. Microsoft keeps it for compatibility and recommends CONCAT instead.

CONCAT is its replacement. It does the same thing, but it also accepts ranges:

=CONCAT(A2," ",B2)
=CONCAT(A2:D2)

The catch is that =CONCAT(A2:D2) joins the cells with nothing in between, so "125 Main St" and "Denver" come out as 125 Main StDenver. CONCAT has no separator or skip-blanks option. For anything beyond two or three cells, TEXTJOIN is the better tool.

Short version: use & or CONCAT for quick joins, TEXTJOIN when you need a separator, and CONCATENATE only in files that must open in Excel 2016 or older (where & works just as well).

TEXTJOIN: join a range with a separator

TEXTJOIN takes the separator once and applies it between every value:

=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)
  • delimiter is the text to put between values, such as " ", ", " or " | ".
  • ignore_empty is TRUE to skip blank cells or FALSE to keep them.
  • text1, text2... are the cells or ranges to join.

It's at its best when some cells are empty. With a street in A, an apartment number in B (often blank), a city in C and a state in D:

  1. Click E2.
  2. Type =TEXTJOIN(", ",TRUE,A2:D2) and press Enter.
  3. Fill the formula down.

Row 2 returns 125 Main St, Apt 4B, Denver, CO. Row 3 has no apartment, and instead of "48 Oak Ave, , Austin, TX" you get 48 Oak Ave, Austin, TX, because TRUE skips the blank cell. With & you'd need an IF for every optional field.

Excel worksheet with Street, Unit, City and State in columns A to D and the formula =TEXTJOIN(", ",TRUE,A2:D2) in the formula bar for cell E2, which returns 125 Main St, Apt 4B, Denver, CO; row 3 has an empty Unit cell and returns 48 Oak Ave, Austin, TX without a double comma

Add spaces, commas and line breaks

Excel never adds a separator for you, so every space or comma has to be in the formula:

You want& formulaTEXTJOIN formula
Emma Johnson=A2&" "&B2=TEXTJOIN(" ",TRUE,A2:B2)
Johnson, Emma=B2&", "&A2=TEXTJOIN(", ",TRUE,B2,A2)
Emma-Johnson=A2&"-"&B2=TEXTJOIN("-",TRUE,A2:B2)
Each value on its own line=A2&CHAR(10)&B2=TEXTJOIN(CHAR(10),TRUE,A2:C2)

CHAR(10) is the line-break character. The formula inserts it, but the cell only shows separate lines once wrapping is on: select the cell and click Home → Wrap Text. Without Wrap Text, the lines run together on one row.

Keep dates and numbers formatted with TEXT

When you concatenate a date or a formatted number, Excel uses the raw value underneath, not what you see in the cell. A ship date of 11/24/2026 in B2 turns into its serial number:

="Order "&A2&" ships on "&B2

returns Order 1042 ships on 46350. Wrap the date in the TEXT function and give it a format code:

="Order "&A2&" ships on "&TEXT(B2,"mm/dd/yyyy")

Now it returns Order 1042 ships on 11/24/2026.

Excel worksheet with Order 1042 and ship date 11/24/2026 in columns A and B, and the formula ="Order "&A2&" ships on "&TEXT(B2,"mm/dd/yyyy") in the formula bar for cell C2, returning Order 1042 ships on 11/24/2026, compared with the result without TEXT, Order 1042 ships on 46350

The same fix works for currency, percentages and decimals. If C2 holds 1250.5 formatted as currency, =A2&" owes "&C2 shows Emma owes 1250.5. These format codes keep it readable:

  • TEXT(C2,"$#,##0.00") gives $1,250.50
  • TEXT(B2,"mmmm d, yyyy") gives November 24, 2026
  • TEXT(D2,"0%") turns 0.15 into 15%
  • TEXT(E2,"0.0") rounds to one decimal place

Format codes follow your regional settings, so mm/dd/yyyy is the US order. Use dd/mm/yyyy if that's how dates are written where you are.

Combine a whole column into one cell

To turn a list into a single comma-separated line, for example email addresses in A2:A20, use:

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

The TRUE skips empty rows, so you can point it at a longer range than you need and it won't leave trailing commas. Use "; " as the separator if you're pasting addresses into an email client that expects semicolons. If you need plain text rather than a live formula, copy the cell and use Home → Paste → Paste Values.

CONCAT can do this too (=CONCAT(A2:A20)), but without a separator the values run together.

Flash Fill: combine without a formula

Flash Fill copies a pattern you type by hand:

  1. In C2, type the result for the first row exactly as you want it, for example Emma Johnson.
  2. Click C3 and press Ctrl+E, or go to Data → Flash Fill.
  3. Check a few rows. Excel fills the rest of the column from your example.

It's fast for one-off cleanup, but the result is plain text. If a first name changes later, the full name doesn't update. Use a formula when the source data will keep changing.

How to reverse it: split text into columns

To go the other way, from "Emma Johnson" back to two cells:

  • TEXTSPLIT (Microsoft 365, Excel 2024 and Excel for the web): =TEXTSPLIT(C2," ") spills "Emma" and "Johnson" into two cells side by side.
  • Text to Columns (every version): select the column, go to Data → Text to Columns, choose Delimited, tick the separator (for example Space or Comma), and click Finish. The results go into the columns to the right, so make sure they're empty first.
  • Flash Fill also works in reverse: type "Emma" next to "Emma Johnson" and press Ctrl+E.

Concatenate in Google Sheets

Google Sheets supports the same approaches, with one difference:

  • & works exactly as in Excel: =A2&" "&B2.
  • =CONCATENATE(A2," ",B2) and =TEXTJOIN(", ",TRUE,A2:D2) work too.
  • CONCAT in Google Sheets joins only two values, so =CONCAT(A2,B2) works but =CONCAT(A2," ",B2) returns an error. Use & or TEXTJOIN instead.
  • To split text, use =SPLIT(A2," ") or Data → Split text to columns.

Why your concatenate formula isn't working

You get #NAME?. CONCAT and TEXTJOIN don't exist in Excel 2016 and older versions without a Microsoft 365 subscription. If someone opens your file in one of those, the formula bar shows _xlfn.TEXTJOIN and the cell shows #NAME?. Rewrite the formula with & if the file must work there. A #NAME? can also come from a typo in the function name or from text without quotation marks, such as =Dear&A2 instead of ="Dear "&A2.

The formula shows as text instead of a result. The cell was formatted as Text before you typed the formula. Change it to General in the Number Format box on the Home tab, then click into the cell, press F2 and Enter. If every formula on the sheet shows as text, Show Formulas is on: press Ctrl+` or click Formulas → Show Formulas to turn it off.

A date turned into a five-digit number. Excel joined the date's serial number. Wrap it in TEXT, as in TEXT(B2,"mm/dd/yyyy").

The words run together. You left out the separator. Add " " between the cells: =A2&" "&B2, not =A2&B2.

Line breaks don't appear. CHAR(10) is in the result, but Wrap Text is off. Select the cells and click Home → Wrap Text.

#VALUE! from TEXTJOIN or CONCAT. The combined result is longer than 32,767 characters, the most a cell can hold.

Frequently Asked Questions

What is the difference between CONCAT and CONCATENATE? CONCAT accepts ranges such as A2:D2, while CONCATENATE only accepts individual cells and text. CONCAT is the newer replacement; CONCATENATE stays for compatibility with older versions of Excel.

How do I concatenate with a space in Excel? Put " " between the cells: =A2&" "&B2 or =CONCAT(A2," ",B2). With TEXTJOIN, make the space the delimiter: =TEXTJOIN(" ",TRUE,A2:C2).

What's the opposite of concatenate in Excel? Splitting. Use =TEXTSPLIT(A2," ") in Microsoft 365, or Data → Text to Columns in any version.

Can I concatenate a range of cells in Excel? Yes, with =CONCAT(A2:A20) (no separator) or =TEXTJOIN(", ",TRUE,A2:A20) (with one). The & operator and CONCATENATE need each cell listed separately.

How do I keep leading zeros or number formats when concatenating? Use TEXT with a format code. For a ZIP code stored as a number, TEXT(A2,"00000") keeps it at five digits; for money, TEXT(A2,"$#,##0.00").

Is there a TEXTJOIN alternative for older Excel? Not a single function. Use & with each separator written out, like =A2&", "&B2&", "&C2.

Let AI write the formula

Concatenate formulas are simple once you know the pieces, but the quotes, separators and TEXT format codes are easy to get slightly wrong. CoreGPT for Excel lets you describe the result instead, like "combine first and last name with a space" or "flag each rep as above or below target", and writes the formula into your sheet so you can check it. It's free to start and works in Excel for Windows, Mac and the web.

Try it: CoreGPT for Excel

Related reading:

Free AI tools

Keep reading

Microsoft 365

How to Wrap Text in Excel (and Start a New Line in a Cell)

Wrap text so long entries fit inside a cell, start a new line exactly where you want with Alt+Enter (Option+Return on a Mac), add line breaks inside formulas with CHAR(10), remove them again, and fix rows that don't resize, in Excel for Windows, Mac, the web and mobile.

Read more