Count numbers in a cell?

[Gregorys05]

Hi Guy's
Im looking for a formula that will count how many numbers are in a cell.
E.G

123456 = 6
1A2B3C = 3
11_B-I = 2

Any ideas?

 

[barry houdini]

If data is in A1 try this formula in for a count of digits

=SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,{1,2,3,4,5,6,7,8,9,0},"")))

 

[T. Valko]

Hi Guy's
Im looking for a formula that will count how many numbers are in a cell.
E.G

123456 = 6
1A2B3C = 3
11_B-I = 2

Any ideas?

One way...

Array entered**:

=COUNT(-MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

 

[MrKowz]

A UDF alternative:

Public Function CountNumeric(rCell As Range) As Long
Dim i As Long
For i = 1 To Len(rCell) - 1
    If IsNumeric(Mid(rCell.Value, i, 1)) Then
        CountNumeric = CountNumeric + 1
    End If
Next i
End Function

 

[Gregorys05]

Thanks guys, the UDF is nearly there, but it is counting the following values as 6 and not 7
<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=64 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>1036330</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=64 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>1036350</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>1036390</TD></TR></TBODY></TABLE>

 

[MrKowz]

Sorry, I got the upper bound wrong:

Public Function CountNumeric(rCell As Range) As Long
Dim i As Long
For i = 1 To Len(rCell)
    If IsNumeric(Mid(rCell.Value, i, 1)) Then
        CountNumeric = CountNumeric + 1
    End If
Next i
End Function

 

[Gregorys05]

Cheers, that works a treat