Counting digits

izzyq

New Member
Joined
Mar 24, 2002
Messages
20
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.
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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.
 
Upvote 1
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
e.g.<o:p></o:p>
<o:p></o:p>
A1: 65306447 (1st time)
B2: 65306464 (2nd time)
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
What would be the formula then? thanks.
 
Upvote 0
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-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
e.g.<o:p></o:p>
<o:p></o:p>
A1: 65306447 (1st time)
B2: 65306464 (2nd time)

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

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top