return numbers in a cell based upon words count in a cell

Enter formula in G2 and copy down

Formula:

=LEN(SUBSTITUTE(A2,"0",""))

It looks like you want to count non-zero characters in the cell.

If so, try this:

=LEN(SUBSTITUTE(A2,0,""))

Copy down as needed.

For something like this you also use SUBSTITUTE function to remove "000"

Formula:

=SUBSTITUTE(A1,"000","")

v	A	B
1	101002000	101002

I interpreted a little differently. This removes all zeroes to the right of the last non-zero number.

It must be array entered.

Formula:

=REPLACE(A1,MAX(ISNUMBER(1/MID(A1,ROW($1:$25),1))*ROW($1:$25))+1,99,"")

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Row\Col	A	B	C
1	101002000	101002	In B1 =REPLACE(A1,MAX(ISNUMBER(1/MID(A1,ROW($1:$25),1))*ROW($1:$25))+1,99,")
2	1010020	101002	And fill down.
3	10100200000	101002
4	101001000	101001
5	1010010	101001
6	10100700000	101007
7	101002000	101002

Or try this ...

=LOOKUP(2,1/(MOD(A1/10^(ROW($1:$25)-1),1)=0),A1/10^(ROW($1:$25)-1))

Another way ...

=LEFT(A1,AGGREGATE(14,6,ROW($1:$25)/(--MID(A1,ROW($1:$25),1)>0),1))

Using ROW($1:$25) leaves the formula vulnerable to new row insertions.

ROW(INDIRECT("1:"&LEN(A1))) would be more robust.

@ Tony

Yes. Or the non-volatile

ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)))

Here is another way to remove trailing zeros

Formula:

=--SUBSTITUTE(--("."&A1),".","")

v	A	B
1	10102000	10102
2	101020300400000	1010203004
3	4560654000	4560654
4	12345	12345