Text function
CONCATENATE
Join text together.
Purpose
Join separate values into one
Returns
One piece of text, the arguments run together in order
Syntax
=CONCATENATE(text1, [text2], ...)
| Argument | Required | What goes here |
|---|---|---|
text1 | Required | A cell, or some text in double quotes. |
text2 | Optional | A cell, or some text in double quotes. |
How to use CONCATENATE
CONCATENATE joins everything you give it, in the order you give it, with nothing between. Any separator you want has to be an argument of its own, in quotation marks.
The forgotten space is the usual bug: joining a first and last name without " " between them gives one run-on word.
The ampersand does the same job in less space. Which you write is a matter of habit, not of behaviour.
Examples
Every result below is worked out by the same evaluator that marks your answers, on the sheet shown here.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Reference | Supplier name | Amount | Posted |
| 2 | GL-6100-8801 | brightwell paper | 1500 | Y |
| 3 | GL-6200-8802 | calder UTILITIES | 320 | y |
| 4 | GL-6100-8803 | meridian freight ltd | 2750 | |
| 5 | GL-6300-8804 | ashgrove catering | 184 | Y |
| 6 | GL-6200-8805 | THORNBURY print | 760 | |
| 7 | GL-6100-8806 | Brightwell Paper | 1120 | Y |
| Formula | Result |
|---|---|
=CONCATENATE(A2," ",PROPER(TRIM(B2)))Reference and cleaned name, with a space put in deliberately. | GL-6100-8801 Brightwell Paper |
=CONCATENATE(LEFT(A2,2),"/",MID(A2,4,4))Building a new key out of pieces of an old one. | GL/6100 |
=A2&" "&PROPER(TRIM(B2))The same answer written with ampersands. | GL-6100-8801 Brightwell Paper |
Notes
- Numbers joined into text stop being numbers. A total built out of a joined column will not add up.
- Excel's newer TEXTJOIN takes a separator once and applies it between every value, which is easier when there are many pieces.
Related
Practise CONCATENATE in Unit 03
A reference tells you what it does. A unit makes you use it.