The formula deletes blank cells and cells with errors. It doesn´t matter if the cells contain numbers or text, they all will be presented in a new column.

Array formula in cell D3:

=INDEX($B$3:$B$20, SMALL(IF(ISBLANK($B$3:$B$20)+ISERROR($B$3:$B$20), "", ROW($B$3:$B$20)-MIN(ROW($B$3:$B$20))+1), ROW(1:1)))

How to create an array formula

  1. Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar. See picture below.
  2. Press and hold Ctrl + Shift.
  3. Press Enter once.
  4. Release all keys.

How to copy array formula

  1. Copy (Ctrl + c) cell D3
  2. Paste (Ctrl + v) array formula on cell range D3:D11

Download excel sample file for this tutorial

(Excel 97-2003 Workbook *.xls)

Functions used in this tutorial:

SMALL(array, k) returns the k-th smallest number in this data set.

returns the row number of a reference

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

Returns the reference specified by a text string