Question: How do I sort text and numbers and also removing blanks using an array formula? See sort-numbers-and-text-cells1picture of the list to the right.

Answer:

Array formula in B2:B15:

=INDEX(List, MATCH(SMALL(IF(ISBLANK(List), "", IF(ISNUMBER(List), COUNTIF(List, "<"&List), COUNTIF(List, "<"&List)+SUM(IF(ISNUMBER(List), 1, 0))+1)), ROW()-ROW(sorted_list_start)+1), IF(ISBLANK(List), "", IF(ISNUMBER(List), COUNTIF(List, "<"&List), COUNTIF(List, "<"&List)+SUM(IF(ISNUMBER(List), 1, 0))+1)), 0)) + CTRL + SHIFT + ENTER

I have used named ranges for easy customization.

Named ranges
List (A2:A15)
sorted_list_start (B2)
What is named ranges?

How to customize the formula to your excel workbook
Change the named ranges.

sort-numbers-and-text-cells

Download excel example file
sort-numbers-and-text-cells-using-excel-array-formula.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 row 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

Share and Enjoy:
  • Share/Bookmark

Related posts:

  1. Sorting numbers and text cells descending also removing blanks using array formula in excel
  2. Sorting text cells using array formula in excel
  3. Sort text cells alphabetically from two columns using excel array formula
  4. Extract numbers and text from a range using array formula in excel
  5. Create a drop down list containing only unique distinct alphabetically sorted text values using excel array formula
  6. Extract a unique distinct list sorted alphabetically removing blanks from a range in excel
  7. Sort a range from A to Z using array formula in excel
  8. Extract and sort text cells from a range containing both numerical and text values
  9. Create unique distinct list sorted based on text length using array formula in excel
  10. Sort text values by length using array formula in excel