Text
Check if a cell contains any number (0–9) and count digits
Detect embedded numeric digits inside alphanumeric codes or passwords—where ISNUMBER returns FALSE—by stripping digits 0–9.
Why ISNUMBER("Suite 204B") returns FALSE
ISNUMBER tests whether the entire cell is stored as a numeric value, so alphanumeric text like "Pass9word!" or "Suite 204B" returns FALSE.
Stripping characters "0" through "9" with SUBSTITUTE and comparing LEN(A2) > LEN(stripped) detects whether at least one digit appears anywhere in the string—and LEN(A2) - LEN(stripped) counts the exact number of digits.
| InputCode (A) | Stripped Text | Digit Count | Contains Digit? |
|---|---|---|---|
| Pass9word! | Password! | 1 | TRUE |
| AdminOnly | AdminOnly | 0 | FALSE |
| Suite 204B | Suite B | 3 | TRUE |
Boolean digit-presence check and exact digit count
"Pass9word!" shrinks by 1 character (TRUE), "AdminOnly" shrinks by 0 (FALSE), and "Suite 204B" shrinks by 3 characters (3 digits).
=IF(COUNTBLANK(A2:A2)=1,FALSE,LEN(A2) > LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"0",""),"1",""),"2",""),"3",""),"4",""),"5",""),"6",""),"7",""),"8",""),"9","")))Count total embedded digits
Subtracting the stripped length from LEN(A4) counts all digits (including repeated zeros).
=IF(COUNTBLANK(A4:A4)=1,0,LEN(A4) - LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A4,"0",""),"1",""),"2",""),"3",""),"4",""),"5",""),"6",""),"7",""),"8",""),"9","")))Try the example
Use the original values or change them to test your understanding.