CODEWITHSUNDEEP
excelexcel-formula
jufg
jufg

Reputation: 163

Counting number of leading zeros in cell in Excel

Trying to figure out how to count the number of leading zeros in an excel cell. The value within the cell can be comprised of both numbers and letters or just numbers or just letters. The column is formatted as a text column.

So far I have been able to use

=MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789"))-1

This gets me all the leading zeros correctly for cells containing only numbers but when it is a combination of both digits and letters it also counts the letters.

eg. 00012 = 3 (correct) 000ab = 5 (should be 3)

Is there a way that I can adapt this formula to not count letters?

Upvotes: 1

Views: 4076

Answers (2)

JvdV
JvdV

Reputation: 75870

Try:

=SEARCH(LEFT(SUBSTITUTE(TEXT(A1,"@"),"0",""),1),A1)-1

enter image description here

Upvotes: 4

user11217663
user11217663

Reputation:

Try looking for the first digit/character that isn't a zero and subtract 1.

=AGGREGATE(15, 7, ROW($1:$9)/(MID(A2, ROW($1:$9), 1)<>"0"), 1)-1

enter image description here

Upvotes: 1

Related Questions