VBA code to count zeros right after decimal

[Ria]

Hi fellows:

I am struggling with very tiny code but gave up after spending lots of time. I have a value in a cell e.g. A1 value is: 12.001.
I need vba code that counts number of zeros after decimal. e.g. 12.001 should give number of zers = 2
12.333 should give no zero or zero
145.01 => only one zero.
478.0003 => 3 zeros.

Any help would be appreciated.

Riaz

 

[Marc L]

Hi !​

Function CountDecimalZero&(V)
    SP = Split(V, Application.DecimalSeparator)
    If UBound(SP) = 1 Then CountDecimalZero = Len(SP(1)) - Len(Replace(SP(1), "0", ""))
End Function

Do you like it ? So thanks to click on bottom right Like !
​

 

[Gary's Student]

This small UDF may be what you need:

Public Function ZeroCount(r As Range) As Long
  Dim whatever As String
  ZeroCount = 0
  If InStr(1, r.Text, ".") = 0 Then Exit Function
  whatever = Split(r.Text, ".")(1)
  While Left(whatever, 1) = "0"
      whatever = Right(whatever, Len(whatever) - 1)
      ZeroCount = ZeroCount + 1
  Wend
End Function

 

[Khalid NGO]

Hi Riaz,

If you are okay with formula solution, you can use this too:

=(LEN(A1)-LEN(SUBSTITUTE(A1,0,"")))

Regards,

[edit: just realized it replaces all zeros]

 

[shrivallabha]

You can do this with formula also. Suppose your data is in A1 then following formula will also give the results:
=LEN(MID(A1,FIND(".",A1&".")+1,99))-LEN(SUBSTITUTE(MID(A1,FIND(".",A1&".")+1,99),"0",""))

 

[Ria]

Hi Riaz,

If you are okay with formula solution, you can use this too:

=(LEN(A1)-LEN(SUBSTITUTE(A1,0,"")))

Regards,

[edit: just realized it replaces all zeros]

Hi Khalid:

Thanks for reply. I was looking this kind of simple solution.
Good thing is it works both in excel and in vba.
Bad thing is it counts zeros before decimal. e.g. 20.003505 should give ans: 2, after decimal but it gives: 3, same is e.g. 0.0103 should give: 1 but it gives 2.
Regardless value is negative or positive, it should count zeros after decimal and before hit any digit (1 to 9). if any zero after or in between digits after decimal it should ignore them.

Anyway thanks at lot for reponse.

 

[Ria]

You can do this with formula also. Suppose your data is in A1 then following formula will also give the results:
=LEN(MID(A1,FIND(".",A1&".")+1,99))-LEN(SUBSTITUTE(MID(A1,FIND(".",A1&".")+1,99),"0",""))

Hi Shrivala:

Thanks for reply. I was looking this kind of simple solution.
2 drawbacks, it works only in excel, does not work in vba (using Excel 2003) but I can make workaround in excel only.
When testing value in cell A1. Value of cell A1 is calculated one (has formula in cell A1). Some time there is no zero right after decimal but it still counts 1.
e.g.
WsMaxWsZeros
-6360.54580
1
But if I type/enter same value in different cell (not a calculated) then it works fine. Not sure it is excel problem or what. I only know basics of vba and excel.

Regardless value is negative or positive, it should count zeros after decimal and before hit any digit (1 to 9). if any zero after or in between digits after decimal it should ignore them.

Anyway thanks at lot for response.

Riaz

 

[Ria]

Hi !​

Function CountDecimalZero&(V)
    SP = Split(V, Application.DecimalSeparator)
    If UBound(SP) = 1 Then CountDecimalZero = Len(SP(1)) - Len(Replace(SP(1), "0", ""))
End Function

Do you like it ? So thanks to click on bottom right Like !
​

Thanks for reply. I was trying with simple solutions other posted in here but they have problems. Now I want to try your solution. I know very basic VBA and can manipulate simple code. Here are 2 questions:
How to call this function into other function (in other function this value will be assigned to variable before further procession). How to call function in other function and assign to variable.
2. Where and how in this function we specify cell e.g. A1, whose value will be tested.

Appreciate your response and help.

Thanks,

Riaz

 

[Marc L]

As I can't guess other function, just call it like any function !

A demonstration with A1 cell from active worksheet :​

Function CountDecimalZero&(V)
    SP = Split(V, Application.DecimalSeparator)
    If UBound(SP) = 1 Then CountDecimalZero = Len(SP(1)) - Len(Replace(SP(1), "0", ""))
End Function

Sub Demo1()
    Z& = CountDecimalZero([A1])
    MsgBox Z
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[Ria]

As I can't guess other function, just call it like any function !

A demonstration with A1 cell from active worksheet :​

Function CountDecimalZero&(V)
    SP = Split(V, Application.DecimalSeparator)
    If UBound(SP) = 1 Then CountDecimalZero = Len(SP(1)) - Len(Replace(SP(1), "0", ""))
End Function

Sub Demo1()
    Z& = CountDecimalZero([A1])
    MsgBox Z
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

Thanks Mark for feedback. It is good function. One problem you have a idea is: it counts all zeros after decimal point. I need to count zeros in between decimal/after decimal and first numeral. E.g. 2.03502, it should give me one zero which is right after decimal and before 3. and ignore second zero. Like 0.00205 should give count 2 zero (between decimal and 2), should ignore 3 zero between 2 & 5. It you make it happen then my problem is solved.

Thanks taking time.

 

[Marc L]

If initial post were like this last one …

You do not ever try Gary's code, am I wrong ?
​

 

[shrivallabha]

Solutions you normally get are based on sample you provide. Your first post did not make this requirement clear to me. Please try below formula and see if it helps.
=MIN(FIND({1,2,3,4,5,6,7,8,9},MID(A1,FIND(".",A1&".")+1,99)&"123456789"))-1

UDF posted by Gary's student is also based on this interpretation.

 

[Marc L]

Function CountDecimalZero%(V)
    SP = Split(V, Application.DecimalSeparator)
    If UBound(SP) = 1 Then While Mid(SP(1), CountDecimalZero + 1, 1) = "0": CountDecimalZero = CountDecimalZero + 1: Wend
End Function

Do you like it ? So thanks to click on bottom right Like !
​

 

[Ria]

This small UDF may be what you need:

Public Function ZeroCount(r As Range) As Long
  Dim whatever As String
  ZeroCount = 0
  If InStr(1, r.Text, ".") = 0 Then Exit Function
  whatever = Split(r.Text, ".")(1)
  While Left(whatever, 1) = "0"
      whatever = Right(whatever, Len(whatever) - 1)
      ZeroCount = ZeroCount + 1
  Wend
End Function

Thanks Gary it works.

Thanks all (Marc, Shrivala), tried all options and after going through testing Gary's solution fulfill my requirement. But I appreciate all of you taking time to look at it.

Riaz