Sorting Text in Excel using Formulas

Sorting text is such a day to day activity that it always surprises me why Excel hasn’t provided a simple spreadsheet formula for doing it. Of course you can use the sorting menu command (menu > data > sort) but this requires manual steps (or VBA). Most of the times we get raw textual data from various sources and we need it to be sorted. While fooling with the COUNTIF() formula, I have realized a powerful yet little known feature that can be exploited to sort text using formulas.

We all know that countif() can be used to find the number of cells in a given range matching a criteria. But do you know that you can use COUNTIF() to find the number of cells in a range greater than or less than a particular value?

Well, that is the trick to sorting text. How?

For eg. assume range A1:A10 has c,b,d,f,h,j,e,a,i,g in them. When you write =countif(a1:a10,"<c") you will get 2 as the result.There are 2 cells with value less than “c”. In other words, the sort order of “c” in the given cells is 3 (since it has 2 cells less than “c”)

You can use this on your own list to fetch the alphabetical sort order of each text value like this:

Essentially the sort order formula looks like this: =countif(SORT RANGE, "<="&CURRENT CELL)

Once you have the sort order, arranging the cells in that order is a piece of cake. We just use VLOOKUP to do our job, like this: =VLOOKUP(1,SORT ORDER TABLE,2,FALSE). (PS: if you are worried about unique cells, which you should, then use this formula instead, =VLOOKUP(small(SORT ORDER COLUMN,1),SORT ORDER TABLE,2,FALSE)

What the heck is above formula doing? It is running a vlookup on the table containing original cells and their sort order to fetch the cell with sort order 1 (or the smallest sort order). Replace the 1 with 2 to get the next cell in the alphabetical order.

Download the workbook with alphabetical text sorting using formulas and see this in action.

Bonus tip: Instead of rewriting the vlookup formula with 2, 3, 4 as lookup value you can use excel's row() function to generate those running numbers for you. You just need to subtract correct value from the row().

Also read: Shuffling a list of cells in random order using formulas, More analytics / text processing tweaks