Forum Discussion
Solved
Getting rid of zeros.
So I have a column of numbers in the even numbered cells some of which are zero and the odd numbered cells are blank. For instance I have the numbers 1 , 2 , 3 , 0 , 4 , 0 , 0 , 5 , 6 in cells A2 , A...
Like this?
=IF(ISODD(SEQUENCE(2*COUNT(FILTER($A$2:$A$18,$A$2:$A$18<>0)))), INDEX(FILTER($A$2:$A$18,$A$2:$A$18<>0), (SEQUENCE(2*COUNT(FILTER($A$2:$A$18,$A$2:$A$18<>0)))+1)/2), "")
If your version of Excel supports dynamic arrays, when like
=IF($A$2:$A$18=0,"",$A$2:$A$18)in B2
just adjust your range. Please note, formulas in Excel don't return blank as a value, thus you have an option to return zero or empty string instead.
I need column B to contain just the six numbers without zeros or blanks.SergeiBaklan
Like this?
=IF(ISODD(SEQUENCE(2*COUNT(FILTER($A$2:$A$18,$A$2:$A$18<>0)))), INDEX(FILTER($A$2:$A$18,$A$2:$A$18<>0), (SEQUENCE(2*COUNT(FILTER($A$2:$A$18,$A$2:$A$18<>0)))+1)/2), "")- Thank you so much Sergei...……..that worked perfectly. I thank you kindly.
