Text
Remove leading zeros from numbers and alphanumeric codes ("000408-B" -> "408-B")
Convert zero-padded numeric text with VALUE or strip only leading zeros from alphanumeric SKUs while preserving internal zeros.
Why VALUE() fails on "000408-B" and SUBSTITUTE("0","") breaks "408"
For pure digit strings like "000408", VALUE(A2) (or --A2) immediately converts the text into the number 408. On alphanumeric ERP codes like "000408-B", VALUE throws #VALUE!—and SUBSTITUTE(A2, "0", "") destroys the internal zero ("48-B" instead of "408-B")!
Splitting on "0" with ignore_empty = TRUE—TEXTSPLIT(A2, "0", , TRUE)—isolates the first non-zero segment ("4"). Slicing MID(A2, FIND(tok, A2), LEN(A2)) strips strictly the leading zeros and leaves every subsequent "0" untouched.
| RawCode (A) | VALUE(A) (Pure Digits) | Alphanumeric Leading-Zero Strip |
|---|---|---|
| 000408-B | #VALUE! | 408-B (internal 0 preserved!) |
| 000408 | 408 (number) | 408 (text) |
| 0000 | 0 | 0 |
Alphanumeric-safe and numeric leading-zero formulas
Guard all-zero strings ("0000") so they return "0" rather than an empty string.
=IF(A2="", "", LET(tok, IFERROR(INDEX(TEXTSPLIT(A2, "0", , TRUE), 1, 1), ""), IF(tok="", "0", MID(A2, FIND(tok, A2), LEN(A2)))))Pure-digit text to real number
Use VALUE(A2) when every cell contains only digits and you want numeric math.
=IFERROR(VALUE(A2), A2)Inputs and interpretation
Empty input remains empty, while a nonempty all-zero code becomes the text 0. This strips only literal leading ASCII zeros, not spaces or signs. VALUE is a conversion function, not a digits-only validator, and can interpret other numeric formats. Preserve identifiers as text when they need more than about 15 significant digits or retain meaningful leading zeros.
Try the example
Use the original values or change them to test your understanding.