Text function
LEFT
Take characters from the start.
Purpose
Take characters from the start of a value
Returns
The first characters of the value, as text
Syntax
=LEFT(text, [how_many])
| Argument | Required | What goes here |
|---|---|---|
text | Required | The cell you are taking characters from. |
how_many | Optional | How many characters to take. One if you leave it out. |
How to use LEFT
LEFT takes the value and how many characters you want from the front. Leave the count out and you get one character.
A fixed count is fine for a prefix that is always the same width, like the two-letter ledger tag on a reference.
For anything that varies, cut at a marker instead: FIND the separator and take one character fewer than its position.
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 |
|---|---|
=LEFT(A2,2)The ledger prefix, always two characters. | GL |
=LEFT(A2,FIND("-",A2)-1)The same answer without assuming the width. This is the version that survives a new reference format. | GL |
=LEFT(A2,7)Prefix and account code together, if what you want is the whole stem. | GL-6100 |
Notes
- Asking for more characters than the value holds gives the whole value rather than an error.
- LEFT returns text. Wrap it in VALUE if the result has to behave as a number.
Related
Practise LEFT in Unit 10
A reference tells you what it does. A unit makes you use it.