EXCEL - How To Count The Number of Digits In a Cell

3
votes

Could anyone enlighten me on how to count the number of digits (numbers) in a cell in Excel?

I have tried LEN and SUBSTITUTE and could not get it to work..

For example a cell has a mix of text and numbers: b72a037h, the formula would take into account 7,2,0,3,7 and return 5.

Is it possible to create such a formula?

Many thanks in advance.

2
excel-formula
Helcome to Stackoverflow. When the answer below solves your problem you should accept it (look: stackoverflow.com/help/someone-answers) – marsh-wiggle

2 Answers

3
votes

One approach would be to remove all digits one by one using SUBSTITUTE, and then compare the resulting length against the original length. The difference would give the number of digits:

=LEN(A1) - LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
    SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, CHAR(48), ""), CHAR(49), ""), CHAR(50), ""),
    CHAR(51), ""), CHAR(52), ""), CHAR(53), ""), CHAR(54), ""), CHAR(55), ""), CHAR(56), ""),
    CHAR(57), ""))
2
votes

Or try this array formula (CTRL+SHIFT+ENTER)

=COUNT(-MID(A1,ROW($1:$99),1))