arraysif-statementgoogle-sheetsarray-formulasdivide

Remove "0's" from column

How can I adjust this formula to remove the repeating "0's" when the cell is empty. I want it to include any cell that has a number, even 0, but exclude cells that are empty.

=ArrayFormula({"Price"; query(value(Misc.!H2:H19), "where Col1 is not null",)})

=ArrayFormula({"Price"; query(value(Misc.!H2:H19), "where Col1 <> 0",)})

Tried both of these with no luck. I'm sure I'm missing something very minor.

enter image description here

enter image description here

Solution

  • divide by 1 and again by 1 to force an error and then hide it:

    =ARRAYFORMULA({"Price"; IFERROR(1/(1/(VALUE(Misc.!H2:H19))))})

    or:

    =ARRAYFORMULA({"Price"; IF(A2:A="",,VALUE(Misc.!H2:H19))})