text-values2I am inspired once again by the article Sorting Text in Excel using Formulas at Pointy haired Dilbert. In Chandoo´s article he sorts text with a "helper" column. My goal with this article is to show you how to sort text cells alphabetically without a helper column.

Here is a example list containing text values in column A.

Here is how to automatically sort text cells without any user interaction in B2.

=INDEX(List, MATCH(SMALL(COUNTIF(List, "<"&List), ROW(1:1)), COUNTIF(List, "<"&List), 0)) + Ctrl + Shift + Enter copied down as far as needed.

Named ranges

List (A2:A15)
What is named ranges?

How to implement array formula to your workbook

Change the named range.

Download excel sample file for this tutorial

Sorting-text-cells-using-array-formulas.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

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

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

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

Read more related articles

  • Share/Bookmark

Related posts:

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