How to Concatenate in Excel: &, CONCAT, TEXTJOIN
· 7 min read
To concatenate in Excel means to join text from several cells into one. The quickest way is the ampersand, &, which glues values together in the order you write them. Anything you want between them, such as a space or a comma, goes in double quotes:
=A2 & " " & B2Two functions do the same job with more options. CONCAT joins a list of values or a whole range. TEXTJOIN adds a separator between every value and can skip empty cells. All three return text.
Join text with & and CONCAT
A product list with a name, a pack size and a unit. Columns E and F build a label two ways, and columns G and H show what happens to a price when you join it.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Product | Size | Unit | Price | With & | With CONCAT | Price, no TEXT | Price with TEXT |
| 2 | Copy paper | 500 | sheets | 4.50 | Copy paper 500 sheets | Copy paper (500 sheets) | Copy paper - 4.5 | Copy paper - $4.50 |
| 3 | Paper cups | 50 | cups | 3.25 | Paper cups 50 cups | Paper cups (50 cups) | Paper cups - 3.25 | Paper cups - $3.25 |
| 4 | Bin bags | 20 | bags | 6.00 | Bin bags 20 bags | Bin bags (20 bags) | Bin bags - 6 | Bin bags - $6.00 |
| 5 | Kitchen roll | 6 | rolls | 7.80 | Kitchen roll 6 rolls | Kitchen roll (6 rolls) | Kitchen roll - 7.8 | Kitchen roll - $7.80 |
E2 holds:
=A2 & " " & B2 & " " & C2Result: Copy paper 500 sheets. Each " " adds one space. Leave them out and you get "Copy paper500sheets", which is the most common first attempt.
CONCAT takes the same pieces as a list, separated by commas:
=CONCAT(A2, " (", B2, " ", C2, ")")Result: Copy paper (500 sheets). The brackets and spaces are just more text in quotes.
& vs CONCAT vs CONCATENATE
They give identical results for a handful of cells, so pick whichever is easier to read. The differences:
&works in every version and in Google Sheets. It is short for two or three pieces and gets hard to read after that.- CONCAT (Excel 2019 and later, Microsoft 365, Excel for the web) accepts ranges:
=CONCAT(A2:C2)joins three cells with nothing between them. - CONCATENATE is the older function. The CONCATENATE Excel function still works for compatibility, but it does not accept ranges, so you have to list every cell. Use CONCAT or
&in new work.
Keep number and date formats with TEXT
Joining a number uses its underlying value, not the format you see. That is why G2 shows Copy paper - 4.5 even though D2 is formatted as 4.50, and G4 shows Bin bags - 6. TEXT turns the number into text in the format you choose before it is joined:
=A2 & " - " & TEXT(D2, "$0.00")Result: Copy paper - $4.50, and Bin bags - $6.00 on row 4.
Dates have the same problem in a bigger way. A date is stored as a number, so ="Delivered on " & B2 gives something like "Delivered on 45730". Use a date format code:
="Delivered on " & TEXT(B2, "d mmm yyyy")That returns "Delivered on 14 Mar 2025". Other useful codes are "0%" for percentages and "#,##0" for thousands separators. Our date formulas guide explains why dates behave like numbers.
Excel formula to join text with a delimiter: TEXTJOIN
TEXTJOIN puts the same separator between every value, and its second argument tells it to skip empty cells:
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)That makes it the right tool for addresses, where some rows have a second line and some do not:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Street | Line 2 | City | Postcode | With & | With TEXTJOIN |
| 2 | 12 Mill Lane | Unit 4 | Leeds | LS1 4AP | 12 Mill Lane, Unit 4, Leeds, LS1 4AP | 12 Mill Lane, Unit 4, Leeds, LS1 4AP |
| 3 | 88 Harbour Road | Bristol | BS1 5TT | 88 Harbour Road, , Bristol, BS1 5TT | 88 Harbour Road, Bristol, BS1 5TT | |
| 4 | 3 Station Street | Floor 2 | York | YO1 7DR | 3 Station Street, Floor 2, York, YO1 7DR | 3 Station Street, Floor 2, York, YO1 7DR |
On row 3, the & version returns 88 Harbour Road, , Bristol, BS1 5TT with an empty slot. TEXTJOIN with TRUE returns 88 Harbour Road, Bristol, BS1 5TT. With FALSE it would keep the empty slot too.
TEXTJOIN is in Excel 2019 and later, Microsoft 365, Excel for the web and Google Sheets. It also joins a whole column into one cell, such as every product in a category, which & cannot do without listing each cell.
Line breaks inside a joined cell
To put each part on its own line, use CHAR(10) as the separator, then turn on Wrap Text for the cell:
=TEXTJOIN(CHAR(10), TRUE, A2:D2)Without Wrap Text the line breaks are there but the cell shows everything on one line.
Fix capitals while you join: PROPER, UPPER and LOWER
Imported names and cities often arrive in random capitals. Three functions fix that:
- PROPER gives every word a capital first letter and small letters after it.
- UPPER makes everything capitals.
- LOWER makes everything small letters.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Name (raw) | City (raw) | Name | City | City code | |
| 2 | tom BECKER | LEEDS | Tom Becker | Leeds | LEE | tom.becker@example.com |
| 3 | GRACE okafor | bristol | Grace Okafor | Bristol | BRI | grace.okafor@example.com |
| 4 | ana lindqvist | York | Ana Lindqvist | York | YOR | ana.lindqvist@example.com |
| 5 | jean-luc MOREAU | NEWCASTLE | Jean-Luc Moreau | Newcastle | NEW | jean-luc.moreau@example.com |
=PROPER(A2) returns Tom Becker, and row 5 becomes Jean-Luc Moreau, because PROPER treats the hyphen as the start of a new word. The email in F2 replaces the space with a dot, makes the result lower case and adds the domain: tom.becker@example.com. The city code takes the first three letters and makes them capitals: LEE, BRI, YOR and NEW.
PROPER is not perfect with names. "MCDONALD" becomes "Mcdonald" and "VAN DER BERG" becomes "Van Der Berg". For a customer list, run PROPER and then check the few names it gets wrong by hand.
Common mistakes
Missing spaces. =A2&B2 joins with nothing in between. Add " " wherever a reader expects a gap.
Joining numbers and dates without TEXT. As shown above, the joined text uses the stored value, so prices lose their trailing zeros and dates turn into five-digit numbers. Whenever a number goes into a sentence, wrap it in TEXT.
Spaces that were already in the data. If a name has a trailing space, A2&" "&B2 produces a double space. TRIM the result or the inputs. Our guide to remove spaces in Excel shows how.
Hard-coding values that live in cells. An AI assistant asked for a label like "Copy paper (500 sheets)" may type the size into the formula: =A2&" (500 sheets)". It looks right on the first row and is wrong on every other row. Every part that changes from row to row should be a cell reference, so check a filled-down formula on more than one row.
Using the joined text for maths. The result is text. If you join a quantity into a label, keep the original number column for totals.
FAQ
How do I combine text from two cells in Excel?
Use & with a space between: =A2&" "&B2. Or use =CONCAT(A2, " ", B2). Both return the two values as one piece of text.
How do I combine first and last name in Excel?
With the first name in B2 and the last name in C2, write =B2&" "&C2. If the names are in the wrong case, wrap the whole thing in PROPER.
How do I join text with a comma between each value?
Use TEXTJOIN: =TEXTJOIN(", ", TRUE, A2:D2). The TRUE skips empty cells so you do not get double commas.
Why does my concatenated number lose its formatting?
Joining uses the stored value, not the display format. Wrap the number in TEXT, for example TEXT(D2, "$0.00") or TEXT(B2, "d mmm yyyy") for a date.
What is the opposite of concatenate in Excel?
Splitting text apart. Use LEFT, MID, RIGHT and FIND, or TEXTSPLIT and Text to Columns. See how to extract text from a cell in Excel.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
- Join first and last names
- Label each rep with their region
- Fix shouting names in the CRM
- List each order’s items in one cell