Getting rid of zeros.

[BLOMCHOP]

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.

[#2]

@BLOMCHOP 

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.

[#3]

Hello,

Kindly attach a sample file

[#4]

I need column B to contain just the six numbers without zeros or blanks.@Sergei Baklan 

[#5 · Accepted solution]

@BLOMCHOP 

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),
"")

[#6]

@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,"")  

[#7]

Thank you so much Sergei...……..that worked perfectly. I thank you kindly.

[#8]

@BLOMCHOP , you are welcome, glad to help