Thanks:  0
Likes:  0

1. 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. Hi,

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

Jay

3. 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. 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. 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. 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

7. 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. ## 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.

9. ## 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. ## 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.

## User Tag List

#### Posting Permissions

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