# Count zeros at the end of long numbers

#### loudnoiseman

##### Board Regular
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

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
If you happen to be using 365, this seems to work.
MrExcelPlayground6.xlsx
BC
21111010111111110003
3100101101001000005
410010001110000000000011
Sheet7
Cell Formulas
RangeFormula
C2:C4C2=SUM(IF(VALUE(RIGHT(TEXT(B2,"0"),SEQUENCE(LEN(TEXT(B2,"0")))))=0,1,0))

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))

Hello,

There are many possible approaches, I think:

Count_Trailing_Zeros.xlsx
AB
1Count trailing zeros
2InputOutput
300
410
5101
63002
760003
8700004
Trailing_Zeros
Cell Formulas
RangeFormula
B3:B5B3=LEN(A3)-LEN(SUM(MID(A3,LEN(A3)+1-ROW(INDIRECT("1:"&LEN(A3))),1)*10^(LEN(A3)-ROW(INDIRECT("1:"&LEN(A3))))))
B6B6=(A6<>0)*SUM(--(A6=ROUND(A6,-ROW(INDIRECT("1:15")))))
B7B7=LEN(A7)-LEN(SUM(MID(A7,LEN(A7)+1-ROW(INDIRECT("1:"&LEN(A7))),1)*10^(LEN(A7)-ROW(INDIRECT("1:"&LEN(A7))))))
B8B8=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

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))))))

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?

One way

Book1
BC
21111010111111110003
3100101101001000005
410010001110000000000011
510
601
Sheet1
Cell Formulas
RangeFormula
C2:C6C2=IF(B2=0,1,LEN(B2)-LEN(--("0."&B2))+2)

Looking at @Phuoc's approach my new favourite for counting trailing zeros would be
Excel Formula:
``=(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.

Last edited:
In order to count trailing ones I suggest to use
Excel Formula:
``=(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)``

Replies
1
Views
156
Replies
3
Views
96
Replies
15
Views
554
Replies
4
Views
111
Replies
3
Views
167

### Forum statistics

1,203,052
Messages
6,053,231
Members
444,648
Latest member
sinkuan85 ### 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.

### Which adblocker are you using?    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

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