Author: Oscar Cronquist Article last updated on August 21, 2017

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

Answer:

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:

Extract 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
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:

COUNTIF(range,criteria)
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

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

ROW(reference)
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

ISNUMBER(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