Problem: Remove blank cells from a list of values? How to create a list with no empty cells? I want to create a new list without blanks.

Answer:

In this blog post I´ll provide two solutions on how to remove blank cells:

Remove blank cells (array formula)

Column A is the list with random blank cells. Column B is the list without the blank cells.

Array formula in B1:B9:

=INDEX($A$1:$A$10,SMALL(IF(ISTEXT($A$1:$A$10), ROW($A$1:$A$10),""),ROW(1:10))) + CTRL + SHIFT + ENTER

ISTEXT($A$1:$A$10) checks if the values in the array are text

ROW($A$1:$A$10) returns the row number

SMALL(IF(ISTEXT($A$1:$A$10), ROW($A$1:$A$10),""),ROW(1:10)) returns the k-th smallest value in a data set. In this case 2, 4, 5, 7 and 8.

Alternative array formula in B1:

=INDEX($A$1:$A$8, SMALL(IF(ISBLANK($A$1:$A$8), "", ROW($A$1:$A$8)-MIN(ROW($A$1:$A$8))+1), ROW(1:1))) + CTRL + SHIFT + ENTER copied down as far as needed.

Download excel sample file for this tutorial:
remove-blank-cells.xls
remove-blank-cells-without-num-errors.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

ISTEXT(value)
Checks whether a value is text, and returns TRUE or FALSE

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

Remove blank cells (keyboard shortcut F5)

  1. Select range
  2. Press F5

  3. Click "Special"

  4. Click "Blanks"
  5. Click OK!
  6. Right click on one of the selected blank cells and select "Delete.."
  7. Click "Shift cells up"
  8. Click OK!
Share and Enjoy:
  • Share/Bookmark

Related posts:

  1. Filter text values existing in range 1 but not in range 2 using array formula in excel
  2. Extract and sort text cells from a range containing both numerical and text values
  3. Filter common text values in range 1 and in range 2 using array formula in excel
  4. Extract duplicate text values from a range containing both numerical and text values in excel
  5. Filter unique text values from a range containing both numerical and text values in excel
  6. Create a unique distinct text list from a range containing both numerical and text values in excel