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

remove-blanks.xls
(Excel 97-2003 Workbook *.xls)

Functions used in this tutorial:

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

ROW(reference)
returns the row number of a reference

INDEX(array;row_num;[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

INDIRECT(ref_text,[a1])
Returns the reference specified by a text string

Related posts:

Reverse a list ignoring blanks in excel

Create a unique distinct sorted list containing both numbers text removing blanks in excel

Extract a unique distinct list sorted alphabetically removing blanks from a range in excel

How to automatically create a unique distinct list and remove blanks

Calculate average of last 10 data (possible blanks) in excel