Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 13 of 13

Thread: Counting digits

  1. #11
    New Member
    Join Date
    Jan 2013
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Counting digits

    Quote Originally Posted by s-o-s View Post
    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!

  2. #12
    New Member
    Join Date
    Jan 2013
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Counting digits

    Quote Originally Posted by s-o-s View Post
    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!

  3. #13
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    32,798
    Post Thanks / Like
    Mentioned
    53 Post(s)
    Tagged
    17 Thread(s)

    Default Re: Counting digits

    Quote Originally Posted by ExcelJuggler View Post
    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...
    Code:
    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)
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •