Article updated on August 21, 2017

Question: How do I sort text and numbers and also removing blanks using an array formula?


Sorting numbers and text cells also removing blanks using array formula in excel

Array formula in B2:B15:

=INDEX($A$2:$A$15, MATCH(SMALL(IF(ISBLANK($A$2:$A$15), "", IF(ISNUMBER($A$2:$A$15), COUNTIF($A$2:$A$15, "<"&$A$2:$A$15), COUNTIF($A$2:$A$15, "<"&$A$2:$A$15)+SUM(IF(ISNUMBER($A$2:$A$15), 1, 0))+1)), ROW(A1)), IF(ISBLANK($A$2:$A$15), "", IF(ISNUMBER($A$2:$A$15), COUNTIF($A$2:$A$15, "<"&$A$2:$A$15), COUNTIF($A$2:$A$15, "<"&$A$2:$A$15)+SUM(IF(ISNUMBER($A$2:$A$15), 1, 0))+1)), 0))

Recommended articles:

Filter a unique distinct list and remove blanks

Question: How do I create a unique distinct list from a list containing several blanks? Answer: Cell range B3:B12 contains several […]

Extract a unique distinct list sorted alphabetically and ignore blanks from a range

This is an answer to a question in this blog post: Extract a unique distinct list sorted from A-Z from […]

Sort a column alphabetically

Table of Contents Sort a column using array formula Two columns sorting by the second column Sort alphanumeric values I […]

Create a unique distinct alphabetically sorted list

The array formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. […]

Extract a list of alphabetically sorted duplicates from a column

Question: How do I create a new list of alphabetically sorted duplicates using excel array formula? Answer: Excel array formula […]

How to enter an array formula

  1. Copy array formula (Ctrl + c)
  2. Select cell B2
  3. Click in formula bar
    formula bar
  4. Paste formula (Ctrl + v)
  5. Press and hold CTRL + SHIFT
  6. Press Enter

If you did this correctly, the array formula now begins with a { and ends with a }.

How to copy array formula

  1. Select cell B2
  2. Copy cell B2 (Ctrl + C)
  3. Select cell range B3:B14
  4. Paste (Ctrl + v)

Download excel example filesort-numbers-and-text-cells-using-excel-array-formulav2.xls
(Excel 97-2003 Workbook *.xls)

Functions used in this blog post:

Counts the number of cells within a range that meet the given condition

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

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

Returns the k-th smallest number in this data set.

Returns the rownumber of a reference

MATCH(lookup_value, lookup_array, [match_type])
Returns the relative position of an item in an array that matches a specified value

Checks whether a value is a number and returns TRUE or FALSE

This blog article is one out of six articles on the same subject.

Read more related articles