Forum Discussion
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 , A4 , A6 , A8 , A10 , A12 , A14 , A16 and A18. In column B I need to transpose these numbers without the zeros so that B2 = 1 , B4 = 2 , B6 = 3 , B8 = 4 , B6 = 5 and B8 = 6. In column A the first and last numbers are never zero. Does anyone have a simple formula that might achieve this? Any help would be appreciated.
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), "")
7 Replies
BLOMCHOP By chance are your numbers going to always be sequential except for the 0's in between the way your example is? I'm guessing not, but if you are doing some sort of counting formula in that column it is possible. I doubt it but if so, the formula could then be (starting in B4):
= IF(AND(B2<MAX(A:A),A4<>""),B2+1,"")- Hello,
Kindly attach a sample file 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), "")
