Page 1 of 2 12 LastLast
Results 1 to 10 of 13
Like Tree1Likes

Counting digits

This is a discussion on Counting digits within the Excel Questions forums, part of the Question Forums category; Is there a function that will allow me to count the number of digits in a cell. I've found the ...

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    Calgary
    Posts
    20

    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

    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
    63,501

    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.
    sunilboreddy likes this.

  4. #4
    New Member
    Join Date
    Mar 2002
    Location
    Calgary
    Posts
    20

    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
    63,501

    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

    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

    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

    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

    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

    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.

Page 1 of 2 12 LastLast

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
  •  


DMCA.com