Count zeros at the end of long numbers

[loudnoiseman]

Hi All,

I'd like to create a formula that would count trailing zeros at the end of long ID numbers, to yield such results for the below examples.

Thanks!!

111101011111111111000	=	3
100101101001000100000	=	5
100100011100000000000	=	11

 

[JamesCanale]

If you happen to be using 365, this seems to work.

MrExcelPlayground6.xlsx
	B	C
2	111101011111111000	3
3	10010110100100000	5
4	100100011100000000000	11
Sheet7

Cell Formulas
Range	Formula
C2:C4	C2	=SUM(IF(VALUE(RIGHT(TEXT(B2,"0"),SEQUENCE(LEN(TEXT(B2,"0")))))=0,1,0))

 

[mumps]

Assuming your numbers start in cell A1, place this formula in cell C1. While holding down the CRTL key and SHIFT key at the same time, press the RETURN key. Copy the formula down column C.
=LEN(A1)-MAX(IFERROR(FIND({1,2,3,4,5,6,7,8,9},A1,ROW(INDIRECT("1:"&LEN(A1)))),0))

 

[Sulprobil]

Hello,

There are many possible approaches, I think:

Count_Trailing_Zeros.xlsx
	A	B
1	Count trailing zeros
2	Input	Output
3	0	0
4	1	0
5	10	1
6	300	2
7	6000	3
8	70000	4
Trailing_Zeros

Cell Formulas
Range	Formula
B3:B5	B3	=LEN(A3)-LEN(SUM(MID(A3,LEN(A3)+1-ROW(INDIRECT("1:"&LEN(A3))),1)*10^(LEN(A3)-ROW(INDIRECT("1:"&LEN(A3))))))
B6	B6	=(A6<>0)*SUM(--(A6=ROUND(A6,-ROW(INDIRECT("1:15")))))
B7	B7	=LEN(A7)-LEN(SUM(MID(A7,LEN(A7)+1-ROW(INDIRECT("1:"&LEN(A7))),1)*10^(LEN(A7)-ROW(INDIRECT("1:"&LEN(A7))))))
B8	B8	=6+RIGHT(TEXT(A8,"0,##############E+00"),2)-LEN(TEXT(A8,"0,##############E+00"))
Press CTRL+SHIFT+ENTER to enter array formulas.

Please note that in English versions of Excel the strings in B8 need to read "0.##############E+00", not "0,##############E+00".

Funnily enough, the non-array formula in B8 is not the fastest one. The formula in B5 (in older versions of Excel to be entered as array formula) is faster, also the one in B6 (but this is a tick slower than B5).

Regards,
Bernd

 

[loudnoiseman]

Thanks Everyone!

I tried them all and Sulprobil's B3 formula seems to work best for me, so thank you very much Bernd:

=LEN(A3)-LEN(SUM(MID(A3,LEN(A3)+1-ROW(INDIRECT("1:"&LEN(A3))),1)*10^(LEN(A3)-ROW(INDIRECT("1:"&LEN(A3))))))

 

[loudnoiseman]

Honestly, I can't tell which part of this formula is searching for zeros.

What would be different if it was looking for trailing 1's instead?

 

[Phuoc]

One way

Book1
	B	C
2	111101011111111000	3
3	10010110100100000	5
4	100100011100000000000	11
5	1	0
6	0	1
Sheet1

Cell Formulas
Range	Formula
C2:C6	C2	=IF(B2=0,1,LEN(B2)-LEN(--("0."&B2))+2)

 

[Sulprobil]

Looking at @Phuoc's approach my new favourite for counting trailing zeros would be

=(B2<>0)*(LEN(ABS(B2))-LEN(--("0."&ABS(B2)))+2)

I call this counting trailing zeros because a single digit 0 would result in 0, not 1.

 

[Sulprobil]

In order to count trailing ones I suggest to use

=(ABS(B2)<>1)*(LEN(SUBSTITUTE(SUBSTITUTE("2"&ABS(B2),"0","2"),"1","0"))-LEN(--("0."&SUBSTITUTE(SUBSTITUTE("2"&ABS(B2),"0","2"),"1","0")))+2)