Count Zeros after comma

[michael.g]

Hi,

How can I count the number of zeros after comma
I mean

0.000406 has 3 zeros
0.376556 has 0 zeros
0.000000434005 has 6 zeros

Thanks

[MickG]

Hi, Try thid UDF
Right click Sheet Tab , Select "View Code". Vb Window appears.
From the VB Toolbar select "Insert", "Module". (New window appears.
Paste code into Blank Window.
Close VB window.
To run Function enter in cell as :-

Please Login or Register  to view this content.

For string in "A1".

Please Login or Register  to view this content.

Regards Mick

[DonkeyOte]

Perhaps (assuming values are valid Excel numbers)

=MAX(0,ROUNDUP(-LOG(A1,10),0)-1)

where A1 etc hold decimal values.

[MickG]

Following on from DonkeOte's very neat idea ,how about:-
To include Decimal Number greater than 1

Please Login or Register  to view this content.

Mick

[DonkeyOte]

Mick, I'm not sure that would work given base 10 decimals - eg 1.01
(why I used ROUNDUP and -1)

A few alternatives for handling numbers >= 1 .. one might be:

=IF(MOD(A1,1),ROUNDUP(-LOG(ROUND(MOD(A1,1),15)),0)-1,0)

No doubt the above can be shortened (assumes 15 digit significance) ... but has added pre-emptive MOD test.

[MickG]

DonkeyOte,
I think you've cracked it now , nice solution
Regards Mick