Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Counting digits

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    Calgary
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  2. #2
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    Can you be more specific? Why won't LEN(cell) work? It should.

    Jay

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,026
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    On 2002-04-15 14:33, izzyq wrote:
    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.
    In A1 enter: 567
    In B1 enter: =LEN(A1), which will give you 3.

    In A2 enter: 56.48
    In B2 enter: =LEN(A2), which will give you 5.

    If you want to avoid counting the decimal point, use:

    =IF(INT(A1)=A1,LEN(A1),LEN(A1)-1)

    will correctly "count" the number of digits in a number that A1 houses, no matter a decimal or an integer.

  4. #4
    New Member
    Join Date
    Mar 2002
    Location
    Calgary
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I was under the impression that you had to type the text string in quotations for it to work. LEN("1111") = 4. I'm out more so for count number of digits in cell C:8? Any idea's

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,026
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    On 2002-04-15 14:48, izzyq wrote:
    I was under the impression that you had to type the text string in quotations for it to work. LEN("1111") = 4. I'm out more so for count number of digits in cell C:8? Any idea's
    =SUMPRODUCT(LEN(A1:A5))

    will total the lengths of the digits/numbers from each cell in A1:A5.

  6. #6
    Board Regular s-o-s's Avatar
    Join Date
    Apr 2002
    Location
    Kissimmee, Florida
    Posts
    384
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

    Hope This Helps.
    Sean.
    Digest of Homes

    WinXP, XL XP

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-15 14:48, izzyq wrote:
    I was under the impression that you had to type the text string in quotations for it to work. LEN("1111") = 4. I'm out more so for count number of digits in cell C:8? Any idea's
    izzyq, perhaps you need an introduction to an Excel concept -- "coercion". Some of Excel operators and functions will "coerce" one data type into another. For example, 1111&"" produces "1111" and "1111"+0 produces 1111. So, when you use =LEN(1111) it coerces 1111 into "1111" and produces 4. So, as Aladin suggested... =LEN(SUBSTITUTE(111.1,".","")) does count the numbers.

  8. #8
    New Member
    Join Date
    Sep 2009
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Counting digits

    Private Function GetDigitCount(inValue As Double) As Double
    GetDigitCount = Int(Log(inValue) / Log(10)) + 1
    End Function

    Private Sub Command1_Click()
    MsgBox GetDigitCount(12345)
    End Sub

    This function count number of digits in a number.
    Instead of 12345 enter your number.

  9. #9
    New Member
    Join Date
    Apr 2010
    Posts
    0
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Counting digits

    hi, the len formula does work on counting the number of occurrences for a single digit in a range of cell, but how about counting the occurrences of double digit appearing?

    e.g.

    A1: 65306447 (1st time)
    B2: 65306464 (2nd time)

    So would I be able to still use the len formula to do a counting to find out the double digit 4 in these range of cells appear 2 times as underlined. Even for like a triple digit 6, so on and so forth.

    What would be the formula then? thanks.

  10. #10
    New Member
    Join Date
    Apr 2010
    Posts
    0
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Counting digits

    hi, the len formula does work on counting the number of occurrences for a single digit in a range of cell, but how about counting the occurrences of double digit appearing?


    e.g.

    A1: 65306447 (1st time)
    B2: 65306464 (2nd time)


    So would I be able to still use the len formula to do a counting to find out the double digit 4 in these range of cells appear 2 times as underlined. Even for like a triple digit 6, so on and so forth.


    What would be the formula then? thanks.

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
  •