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.

 

[Jay Petrulis]

Hi,

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

Jay

 

[Aladin Akyurek]

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.

 

[izzyq]

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

 

[Aladin Akyurek]

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.

 

[s-o-s]

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

 

[Mark W.]

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.

 

[Unlimited Power]

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.

 

[ivan tan]

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?<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
e.g.<o></o>
<o></o>
A1: 65306447 (1st time)
B2: 65306464 (2nd time)
<o></o>
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.<o></o>
<o></o>
What would be the formula then? thanks.

 

[ivan tan]

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?
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
e.g.<o></o>
<o></o>
A1: 65306447 (1st time)
B2: 65306464 (2nd time)

<o></o>
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.
<o></o>
<o></o>
What would be the formula then? thanks.