Text & String
Count Numeric Digits (0–9) Inside an Alphanumeric Text String
Count how many numeric digits (0–9) appear anywhere inside a mixed serial number, SKU, or password string by subtracting digit-stripped length from LEN(A2).
Counting digits inside mixed SKUs and passwords without array-splitting!
Password complexity auditors ("must contain at least 3 digits"), VIN/lot-code validators, and catalog cleaners often need the exact count of 0–9 characters inside an alphanumeric string like "LOT-2026-B49X" (6 digits) or "Pass908!" (3 digits). Stripping all ten numerals 0 through 9 via nested SUBSTITUTE calls and subtracting the remaining length from LEN(A2) works in every spreadsheet version—zero dynamic arrays, zero helper cells, and zero CSE required!
Because LEN(A2) minus the digit-free length equals the exact digit count, dividing by LEN(A2) or comparing >= Min_Digits validates password and SKU formatting rules cleanly even when digits are scattered at the start, middle, and end of the text!
| Input String (A) | Total LEN(A) | Length After Stripping 0–9 | Digit Count |
|---|---|---|---|
| "LOT-2026-B49X" | 13 chars | 7 non-digits ("LOT--BX") | 6 digits |
| "Pass908!" | 8 chars | 5 non-digits ("Pass!") | 3 digits |
| "ALPHA-ONLY" | 10 chars | 10 non-digits | 0 digits |
Exact Count of Numeric Digits (0–9) in a Text String
Subtracts the length of A2 with all digits 0..9 removed from total length LEN(A2).
=LEN(A2) - LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "0", ""), "1", ""), "2", ""), "3", ""), "4", ""), "5", ""), "6", ""), "7", ""), "8", ""), "9", ""))Minimum Digit-Count Policy Check (e.g., >= E2 Digits Required)
Verifies whether string A3 meets the minimum required numeral count in E2.
=IF(LEN(A3) - LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A3, "0", ""), "1", ""), "2", ""), "3", ""), "4", ""), "5", ""), "6", ""), "7", ""), "8", ""), "9", "")) >= E2, "Meets Digit Rule", "Too Few Digits")Try the example
Use the original values or change them to test your understanding.