Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? . |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
add leading zeros within a formula | Excel Worksheet Functions | |||
Ignore leading zeros in a formula cell | Excel Discussion (Misc queries) | |||
LEADING ZEROS IN FORMULA RESULT | Excel Worksheet Functions | |||
leading zeros are not showing in the formula bar but do show in ce | Excel Discussion (Misc queries) | |||
save text field w/ leading zeros in .csv format & not lose zeros? | Excel Discussion (Misc queries) |