Recreate range of cells containing blank cells in new range with blank cells omitted?

[appreciatethehelp]

I’ve been experimenting with using filters to do this, but am curious as to how one might go about doing this with a formula?

The range in question contains numbers and text, and must be recreated in the same order as it was initially, but without the blank cells.

Here’s an example document for illustration.

Omit Blank Cells using Formula.ods

[JohnSUN]

Please read this text and you can not only filter, but also sort (if necessary) your data using formulas.

[JohnSUN]

For your example data, a formula like this works well:

{=INDEX($A$1:$A$1000;SMALL(IF($A$2:$A$1000<>"";ROW($A$2:$A$1000);1000);ROW()-1))}

(This is an array formula, entered with Ctrl+Shift+Enter)