Combine text in Excel: &, CONCAT and TEXTJOIN
Join a first and last name, build a full address, or list every item with commas. When to use &, CONCAT or TEXTJOIN, and how to keep the spaces right.
To combine text in Excel, put an ampersand (&) between the parts:
=A2&" "&B2. CONCAT does the same as a function. TEXTJOIN
joins a whole range with a separator and can skip empty cells.
All 3 give the same answer on easy data. They differ when a cell is empty, and when the range is long. This article tries all 3 on one delivery sheet.
| Row | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | First | Last | Line 1 | Line 2 | Town | Postcode |
| 2 | Jonas | Berg | 12 Mill Lane | Leeds | LS1 4AB | |
| 3 | Amara | Obi | Flat 3 | 40 King Street | York | YO1 7PQ |
| 4 | Lena | Fischer | 7 Park Road | Hull | HU5 2RT |
D2 and D4 are empty. Most real address lists look like this.
The ampersand: a name
=A2&" "&B2 returns Jonas Berg.
The space is text, so it goes in quote marks. Leave it out and
=A2&B2 returns JonasBerg.
Joining adds nothing you did not ask for. Every space, comma and word between the parts must be
in the formula. =B2&", "&A2 returns Berg, Jonas. The comma
and its space are one piece of text.
The ampersand works in every version of Excel. It is the one to learn first.
CONCAT: the same, as a function
=CONCAT(A2," ",B2) returns Jonas Berg.
CONCAT needs Excel 2019 or later. CONCATENATE is the older name, and it still works. Both give the same answer as the ampersand.
CONCAT can also take a range. But it puts nothing between the cells.
=CONCAT(C2:F2) returns 12 Mill LaneLeedsLS1 4AB. For an address,
that is no use.
The address: where the ampersand goes wrong
You want each address on one line, with a comma between the parts. The ampersand way is long:
=C2&", "&D2&", "&E2&", "&F2 returns 12
Mill Lane, , Leeds, LS1 4AB.
D2 is empty, but its comma is still there. On a printed label, that looks careless. It happens on every row with an empty field.
TEXTJOIN: a separator, and empty cells skipped
TEXTJOIN takes 3 things. First the separator. Then TRUE to skip empty cells. Then the range to join.
=TEXTJOIN(", ",TRUE,C2:F2) returns 12 Mill Lane, Leeds, LS1 4AB.
Fill it down. Row 3 returns Flat 3, 40 King Street, York, YO1 7PQ. Row 4 returns 7 Park Road, Hull, HU5 2RT.
You type the separator once, however many cells there are. Change TRUE to FALSE and the empty
cell keeps its place. =TEXTJOIN(", ",FALSE,C2:F2) gives the ", ,"
back.
It also lists a column in one cell.
=TEXTJOIN(", ",TRUE,A2:A4) returns Jonas, Amara, Lena. TEXTJOIN
needs Excel 2019 or later, like CONCAT.
Why a date turns into 45730
Now join a number or a date into a sentence. This is where most people get stuck. Here is one order, in row 8 of the same sheet.
| Row | A | B | C | D |
|---|---|---|---|---|
| 7 | Order | Due | Amount | Discount |
| 8 | 1042 | 14/03/2025 | 1,250.50 | 15% |
="Order "&A8&" is due "&B8 returns Order 1042 is due
45730.
="Total: "&C8 returns Total: 1250.5. The comma and the last
0 are gone.
="Discount: "&D8 returns Discount: 0.15.
Nothing is broken. B8 holds the number 45730, and the cell's format shows it as a date. C8 holds 1250.5, and the format adds the comma and the 0. The format belongs to the cell, not to the value.
When you join, Excel takes the value and leaves the format behind. So you see the plain number. The article on why dates are numbers explains where 45730 comes from.
TEXT puts the format back
TEXT turns a number into text, in the format you write. Put it around the number inside the join.
="Order "&A8&" is due "&TEXT(B8,"d mmm yyyy") returns
Order 1042 is due 14 Mar 2025.
="Total: "&TEXT(C8,"#,##0.00") returns Total: 1,250.50.
="Discount: "&TEXT(D8,"0%") returns Discount: 15%.
The format code goes in quote marks. These are the ones you will use most:
| Format code | What 14 March 2025 or 1250.5 becomes |
|---|---|
| "d mmm yyyy" | 14 Mar 2025 |
| "dd/mm/yyyy" | 14/03/2025 |
| "dddd" | Friday |
| "#,##0.00" | 1,250.50 |
| "0.00" | 1250.50 |
| "0%" | 15% (for 0.15) |
The date letters depend on the language of your Excel. In some languages, the letters for day, month and year are different.
TEXT also fixes a result with too many decimals. Split the amount 3 ways and join it:
="Each: "&C8/3 returns Each: 416.833333333333. With
TEXT(C8/3,"0.00") inside, it returns Each: 416.83.
The result is always text
Whatever comes out of a join is text. So is whatever comes out of TEXT. It will not add up, and it will not sort as a number.
=ISNUMBER(TEXT(C8,"0.00")) returns FALSE.
=A8&B8 returns 104245730, which is text too.
So join for people to read: a label, an email line, a title for a report. Keep the first and last names, the dates and the amounts in their own columns. Do every calculation on those columns.
Which one to use
- 2 or 3 parts: use the ampersand. It is short, and it works in every Excel.
- A range, or cells that can be empty: use TEXTJOIN with TRUE. It is the only one that drops the extra commas.
- A number or a date in the sentence: wrap it in TEXT, whichever way you join.
CONCAT is fine, but it does nothing the other two cannot do. Excel 2016 and older do not have CONCAT or TEXTJOIN. If a file must work there, use the ampersand.
Joining is the opposite of taking text apart. The article on LEFT, RIGHT and MID does that half.
The lessons below use the ampersand, TEXTJOIN and TEXT. Each lesson is a real sheet you type into, and each answer is checked when you press Enter.