Counting digits

[izzyq]

Is there a function that will allow me to count the number of digits in a cell. I've found the "len" funtion that will count the number of characters for text, but nothing for a number.

 

[ExcelJuggler]

I take it Len won't work when there are Characters in the cell.

In which case paste the following UDF (User Defined Function) into a VB Module and call it from Excel in the way of any normal function.


Function HowMany(Rng)
HowMany = 0
If IsNumeric(Rng) Then
HowMany = Len(Rng)
GoTo EndFunc
End If

For x = 1 To Len(Rng)
Ext = Mid$(Rng, x, 1)
If InStr(1, "0123456789", Ext, vbTextCompare) > 0 Then
HowMany = HowMany + 1
End If
Next

EndFunc:
End Function

Thanks a lot, this is just what I need!

 

[ExcelJuggler]

I take it Len won't work when there are Characters in the cell.

In which case paste the following UDF (User Defined Function) into a VB Module and call it from Excel in the way of any normal function.


Function HowMany(Rng)
HowMany = 0
If IsNumeric(Rng) Then
HowMany = Len(Rng)
GoTo EndFunc
End If

For x = 1 To Len(Rng)
Ext = Mid$(Rng, x, 1)
If InStr(1, "0123456789", Ext, vbTextCompare) > 0 Then
HowMany = HowMany + 1
End If
Next

EndFunc:
End Function

Thank you so much s-o-s! Even after more than 10 years, your solution still works perfectly!

 

[Rick Rothstein]

Thank you so much s-o-s! Even after more than 10 years, your solution still works perfectly!

Here is a simpler version of that function for you to use...

Function DigitCount(S As String) As Long
  Dim X As Long
  For X = 1 To Len(S)
    If Mid(S, X, 1) Like "#" Then DigitCount = DigitCount + 1
  Next
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use DigitCount just like it was a built-in Excel function. For example,

=DigitCount(A1)