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

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