Array formula to Identify Leading Zeros.
 

I have a bunch of spreadsheets that have a sizable number of columns -
usually about 128 columns. And I need to identify those columns that
have leading zeros in them. Logically, If I knew how many cells had a
zero in the left most position, I could tell which column had a
leading zero. But with 128 columns and 500-12000 rows, this is too
hard to just eyeball.

I think an array formula will do the trick - but I'm not having much
luck getting one to work. For example, in column B, cells B7:B478,
I've tried something like:
{=sum(countif(B7:B478,left(B7:B478,1)=0))}

Am I in the ballpark here?

Array formula to Identify Leading Zeros.
 

this worked for me -- data formatted as text in c1:I10:

=COUNTIF(C1:I10,"=0*")
--
hope to help,
cm


"Commish" wrote:

I have a bunch of spreadsheets that have a sizable number of columns -
usually about 128 columns. And I need to identify those columns that
have leading zeros in them. Logically, If I knew how many cells had a
zero in the left most position, I could tell which column had a
leading zero. But with 128 columns and 500-12000 rows, this is too
hard to just eyeball.

I think an array formula will do the trick - but I'm not having much
luck getting one to work. For example, in column B, cells B7:B478,
I've tried something like:
{=sum(countif(B7:B478,left(B7:B478,1)=0))}

Am I in the ballpark here?
.

Array formula to Identify Leading Zeros.
 

That is a nice small formula and it works like a charm.

Thanks.


On Dec 21, 5:37*pm, cm wrote:
this worked for me -- data formatted as text in c1:I10:

=COUNTIF(C1:I10,"=0*")
--
hope to help,
cm

"Commish" wrote:
I have a bunch of spreadsheets that have a sizable number of columns -
usually about 128 columns. And I need to identify those columns that
have leading zeros in them. Logically, If I knew how many cells had a
zero in the left most position, I could tell which column had a
leading zero. But with 128 columns and 500-12000 rows, this is too
hard to just eyeball.

I think an array formula will do the trick - but I'm not having much
luck getting one to work. For example, in column B, cells B7:B478,
I've tried something like:
* * * {=sum(countif(B7:B478,left(B7:B478,1)=0))}

Am I in the ballpark here?
.