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.
|Original data||Normally sort result||your wanted sort result|
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.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!