How to sort alphanumeric data starting with numbers in Excel?

If your data in Excel includes entries starting with numbers followed by optional text (e.g., "201510", "201510AA", "201520"), sorting them directly may not produce the order you want. Typically, Excel separates pure numbers and alphanumeric combinations, which can lead to an incorrect sequence. For example, "201510" might sort separately from "201510AA". This guide demonstrates how to sort such alphanumeric data while keeping numerical prefixes in the correct order, ensuring results like "201510", "201510AA", "201520".

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.