About this exercise
A 120-row dataset on one sheet, 3 answer cells, about 6 minutes. Graded in your browser: the value and the formula behind it, with feedback that names the fix.
How to solve it
- E2
=LEFT(A2,3) - F2
=VALUE(MID(A2,5,4)) - G2
=RIGHT(A2,4)
LEFT, MID and RIGHT cut a fixed number of characters out of a text: LEFT(A2,3) takes the first three, RIGHT(A2,4) the last four, and MID(A2,5,4) four characters starting at position 5. They work here because every SKU follows the same 13-character pattern, so the positions never change from row to row. All three return text — which is exactly right for the serial (0114 keeps its zero) but wrong for the year, so the year is wrapped in VALUE to become a real number.
Pitfall 1 (the classic test trap): =MID(A2,5,4) shows 2019 left-aligned — it is text. SUM over the column returns 0, =F2>2022 compares alphabetically, and a filter lists "2019" under text values. VALUE, NUMBERVALUE, *1, +0 or the double unary -- all convert it. Pitfall 2: the opposite mistake on the serial — =VALUE(RIGHT(A2,4)) turns 0142 into 142 and the leading zero is gone for good; keep identifiers as text or re-pad with TEXT(n,"0000"). Pitfall 3: fixed positions break the moment a category code has two or four letters; for variable-width parts use FIND("-",A2) to locate the hyphen (see the e-mail split exercise) or TEXTSPLIT(A2,"-") in Excel 365. MID is forgiving: asking for more characters than remain simply returns what is there, so =MID(A2,10,99) is a common idiom for "everything after position 9". Version note: TEXTBEFORE, TEXTAFTER and TEXTSPLIT (Excel 365 only) do the same job without counting characters; LEFT/MID/RIGHT work in every version and in every other spreadsheet tool. Flash Fill (Ctrl+E) can guess these splits interactively, but it produces static values that do not update when a SKU changes — formulas do.
Other ways that work
- =MID(A2,5,4)*1 or =--MID(A2,5,4) — arithmetic coercion instead of VALUE
- =NUMBERVALUE(MID(A2,5,4)) — explicit conversion, locale-aware
- =MID(A2,10,4) — the serial via MID instead of RIGHT (same text)
- =TEXTBEFORE(A2,"-") / =TEXTAFTER(A2,"-",-1) — Excel 365 only, position-free
Common mistakes
Leaving the year as text: =MID(A2,5,4) returns "2019" (left-aligned, cannot be summed or compared as a number). Wrap it in VALUE or multiply by 1.
How we grade
Any formula that produces the right result is accepted, unless the task names a function: then another function earns half credit. We compare values with a small tolerance and ignore case and spaces in text. A correct number typed by hand instead of a formula earns half credit. Multi-cell tasks earn full credit at 100% of cells, half credit from 50%. Where it matters, we also change an input cell on a copy of your sheet and check that your formula follows.
Answers computed with Excel 365 rules and checked in our grading engine on November 16, 2026. Author: GridBelt team.