Text & Cleaning
Count Text-Stored Numbers With Leading Zeros (ZIP, Routing & Badge Audit Formula)
Detect and count text-stored numeric strings carrying leading zeros (such as "07030" or "0042") without false positives on true numbers, "0", decimals ("0.75"), or alphanumeric codes.
Separating zero-padded numeric text from decimals and alphanumeric IDs
During CSV imports, postal codes like "07030" and bank routing numbers like "021000021" must remain text to preserve leading zeros, whereas invoice amounts stored as "0042" often signal accidental text padding that breaks SUM.
A naive COUNTIF(range, "0*") misfires on single-digit "0", decimal strings like "0.75", and non-numeric part numbers like "0A42". Checking ISTEXT(A2), LEN(A2) >= 2, LEFT(A2, 1) = "0", MID(A2, 2, 1) <> ".", and ISNUMBER(VALUE(A2)) isolates true zero-padded numeric strings with surgical precision.
| Cell Input | Data Type | Starts With "0"? | Passes Leading-Zero Audit? |
|---|---|---|---|
| ="07030" | Text | Yes | TRUE (5-digit NJ postal code) |
| 7030 | Number | No (stripped) | FALSE (true numeric cell) |
| ="0042" | Text | Yes | TRUE (zero-padded integer text) |
| ="0.75" | Text | Yes ("0.") | FALSE (decimal string excluded) |
| 0A42 | Text | Yes | FALSE (alphanumeric token excluded) |
| ="0" | Text | Yes (len 1) | FALSE (single zero excluded) |
Row-Level Zero-Padded Numeric Text Detector
Returns TRUE strictly when A2 is multi-character numeric text starting with "0" (excluding "0.x" decimals).
=AND(ISTEXT(A2), LEN(A2) >= 2, LEFT(A2, 1) = "0", MID(A2, 2, 1) <> ".", ISNUMBER(VALUE(A2)))Count Zero-Padded Numeric Strings Across Column
Counts all rows in B2:B7 flagged as text-stored leading-zero numbers.
=COUNTIF(B2:B7, TRUE)Try the example
Use the original values or change them to test your understanding.