Joining text in Excel: how it works
Join cells with the & sign, for example =A2&" "&B2. For long lists, use TEXTJOIN.
Try the first exercises for freeIn the lesson Joining text
How do you join text?
With the & sign you stick texts together: =A2&B2 puts the contents of B2 straight after those of A2. If you want a space or another character in between, add it as text in quotation marks: =A2&" "&B2.
For a longer series of cells, TEXTJOIN is handier. You say once what should go between each piece and whether empty cells can be left out, and then you give the whole range. CONCAT and the older CONCATENATE join without anything in between.
When do you join text?
- First name and surname together in one cell: =A2&" "&B2.
- A code from a country and a number: =C2&"-"&D2.
- A sentence with an amount: ="Total: "&TEXT(B2,"#,##0.00").
- A list of names in one cell, with commas in between: =TEXTJOIN(", ",TRUE,A2:A6).
Syntax
The syntax is the fixed pattern of a function. The words in the brackets are placeholders: that's where you fill something in yourself, such as a cell, a number or a piece of text. Everything else you type exactly as it is.
Joins with the same character between each piece. Works with a whole range.
Filled in: =TEXTJOIN(" ",TRUE,A1:B1)
This is how to read the formula, part by part:
| Part | What it means | In the example |
|---|---|---|
= | Every formula starts with an equals sign. Without =, Excel treats what you type as ordinary text. | = |
TEXTJOIN | The name of the function: that's how Excel knows what to do. You can type it in lower case, Excel puts it in capitals itself. | TEXTJOIN |
( ) | Straight after the name come the brackets. Inside them is what the function needs to work: the arguments. | ( ) |
delimiter | With TEXTJOIN: what goes between each piece, such as ", " or " ". | " " |
ignore_empty | With TEXTJOIN: TRUE to skip empty cells, FALSE to include them. | TRUE |
text1 | The first cell, the first range or the first text. | A1:B1 |
[text2] | Optional: whatever comes after it. | left out |
, | A comma separates the arguments from each other. | , |
" " | You type text in quotation marks, such as "Leeds". A number or a cell, such as 500 or A2, you type without them. | |
[ ] | Anything in square brackets can be left out. You never type the square brackets themselves. | |
... | You can give more, with a comma between each one. |
Step by step
D1 contains =TEXTJOIN(" ",TRUE,A1:B1). TEXTJOIN puts a space between the cells. Change the numbers or the formula and see what happens.
This is how to go about it:
- Click D1 and type TEXTJOIN with a bracket.
=TEXTJOIN( - Type what should go between the pieces, here a space in quotation marks, and a comma.
=TEXTJOIN(" ", - Type TRUE, so that empty cells are left out, and a comma.
=TEXTJOIN(" ",TRUE, - Select the cells you're joining, A1 and B1, close the bracket and press Enter.
=TEXTJOIN(" ",TRUE,A1:B1)
More examples
With the & sign
D1 contains =A1&" "&B1. A space is text too, so it goes in quotation marks.
A list without empty cells
C1 contains =TEXTJOIN(", ",TRUE,A2:A5). With TRUE, the empty cell is left out. With FALSE, you'd get two commas in a row.
A number with its format
C1 contains ="Total: "&TEXT(A2,"#,##0.00"). Without TEXT, you'd see 1234.5. TEXT decides how the number appears in the sentence.
Where it often goes wrong
- CONCATENATE is the old function and doesn't work with ranges. CONCAT does.
Practise this in the lesson Joining text. The first exercises are free.
Frequently asked questions
Read next
LEFT, RIGHT and MIDTRIMLENUPPER, LOWER and PROPERSEARCH and FIND