How to sort alphanumeric data in Excel?

If you have a list of data which are mixed with both numbers and text strings, when you sort this column data normally in Excel, all the pure numbers are sorted on top and the mixed text strings at bottom. But, your needed result like the last screenshot shown. This article will provide a useful method that you can use to sort alphanumeric data in Excel so that you can achieve the results you want.

Sort alphanumeric data with formula helper column

Original data		Normally sort result		your wanted sort result

Sort alphanumeric data with formula helper column

In Excel, you can create a formula helper column, and then sort the data by this new column, please do as following steps:

1. Enter this formula =TEXT(A2, "###") into a blank cell besides your data, B2, for instance, see screenshot:

2. Then drag the fill handle down to the cells that you want to apply this formula, see screenshot:

3. And then sort the data by this new column, select the helper column you created, then click Data > Sort, and in the popped out prompt box, select Expand the selection, see screenshots:

4. And click Sort button to open the Sort dialog, under Column section, choose Helper column name that you want to sort by, and use Values under Sort On section, and then select the sort order as you want, see screenshot:

5. And then click OK, in the popped out Sort Warning dialog, please select Sort numbers and numbers stored as text separately, see screenshot:

6. Then click OK button, you can see, the data has been sorted to your need.

7. At last, you can delete the contents of the helper column as you need.