Suppose you have a string "abc123def45cd" and you want to count non numbers in this.
If your string is in A1, use following formula in A1
=IF(LEN(TRIM(A1))=0,0,SUMPRODUCT(–NOT(ISNUMBER((–MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))))))
Suppose you have a string "abc123def45cd" and you want to count non numbers in this.
If your string is in A1, use following formula in A1
=IF(LEN(TRIM(A1))=0,0,SUMPRODUCT(–NOT(ISNUMBER((–MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))))))
This formula will also work…
=LEN(A1)-SUMPRODUCT(–ISNUMBER(-MID(A1,ROW($1:$999),1)))
Awesome!!!
Here is another formula will also work….
=LEN(A1)-SUMPRODUCT(–ISNUMBER(-MID(A1,ROW($1:$999),1)))
Awesome!!!