Text function
LEN
Count the characters.
Purpose
Count the characters in a value
Returns
A number: how many characters the value holds
Syntax
=LEN(text)
| Argument | Required | What goes here |
|---|---|---|
text | Required | The cell whose characters you want counted, usually A2. |
How to use LEN
LEN counts everything, including spaces, punctuation and digits. A blank cell gives 0.
On its own it is rarely the answer. It earns its place as a check: a reference that should be twelve characters and comes back as thirteen has something wrong with it, and a name whose length changes when you wrap it in TRIM was padded.
It is also how you work out how many characters are left in a value once you have found a marker in it, which is what MID needs for its third argument.
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 |
|---|---|
=LEN(A2)A well formed reference is twelve characters. Anything else is worth looking at. | 12 |
=LEN(B2)The padded name, counted as it sits in the cell. | 20 |
=LEN(TRIM(B2))The same name once the padding has gone. The gap between the two is the padding. | 16 |
=MID(A2,FIND("-",A2)+1,LEN(A2)-FIND("-",A2))Everything after the first dash. LEN works out how much is left so you do not have to count. | 6100-8801 |
Notes
- LEN sees the stored value, not the formatted one. A number shown as 1,500.00 is still four characters if the cell holds 1500.