Excel track · Exercise 50 of 86
LEN, LEFT and RIGHT: count and cut
Measure a string, then cut fixed pieces from either end.
Beginner
One fifteen-character code
Read-only data · screen 1 of 3 · answer in B2
In B2, count how many characters the code in A2 has, spaces included.
| A | B | |
|---|---|---|
| 1 | Code | Characters |
| 2 | Invoice 2026-03 |
In B2, cut the first three characters of A2 to get the document prefix.
| A | B | |
|---|---|---|
| 1 | Code | Prefix |
| 2 | Invoice 2026-03 |
In B2, cut the last four characters of A2 to get the year and month.
| A | B | |
|---|---|---|
| 1 | Code | Year and month |
| 2 | Invoice 2026-03 |
Reference answer
What it does
LEN counts characters, spaces included. Invoice 2026-03 runs to 15: the seven letters, the space, four year digits, the hyphen and two month digits. Counting the space is what makes 15 right and 14 wrong.
LEFT(A2,3) keeps the first three characters and returns Inv, the document prefix. RIGHT(A2,4) keeps the last four and returns 6-03, the final digit of the year, the hyphen and the month. Both take a count you supply, so neither knows anything about the code.
A typed count is fine while every code has the same shape. The first code with a longer prefix gets cut in the wrong place, and LEFT returns the wrong letters instead of an error. That is the point at which FIND takes over the counting.
Syntax
=LEN(text), =LEFT(text, count), =RIGHT(text, count)Worked example
On One fifteen-character code, =LEN(A2) returns 15.
=LEN(A2)Common errors
| Error | What it means and how to fix it |
|---|---|
#VALUE! | A negative count gives LEFT or RIGHT nothing to cut, so the cell shows #VALUE!. |
Wrong cut | A count that suits one code takes the wrong characters from a code of a different length. |
#NAME? | The function is spelled LN or LEF, and Excel returns #NAME?. |
At work
Splitting a supplier reference into its document type and the period it covers.