While working with Microsoft Excel, we need to use the sorted alphanumeric data. In this article, we will demonstrate the step-by-step procedures to sort alphanumeric data in Excel.
What Is Alphanumeric Data in Excel?
Alphanumeric is a term used to describe data that consists of both numbers and letters. When a user puts data comprised of letters and numbers into a device, this is known as alphanumeric data input. A short string of alphanumeric characters, for example, “AA2B32“. Alphanumeric is a term that is frequently used to describe the text that may be entered or utilized in a field, including an alphabetic password field.
How to Sort Alphanumeric Data in Excel (With Easy Steps)
Suppose, we have a list of alphanumeric data in the number field column B as shown in the picture below. Now, we need to sort those data. Let’s look at the procedure for this.
Step 1: Insert Formula in a Helper Column
- Firstly, we need to add a helper column just beside the number field.
- Secondly, put the formula into that column’s first cell which is C5.
=TEXT(B5, "###")- Thirdly, press Enter to insert the formula into your spreadsheet data.
- The TEXT Function will convert those data into Text format.
- Further, to copy the data over the range, drag the Fill Handle down. Or, instead of doing this, just double-click on the Plus (+) sign to AutoFill the column.
- And, this will duplicate the data into the helper column but in text format.
Read More: How to Add Sort Button in Excel
Step 2: Sort Data by the Helper Column
- After that, sort the data by this new column by selecting the helper column that we just made.
- Then, go to the Data tab from the ribbon.
- Next, click on Sort under the Sort & Filter category.
- This will appear in a pop-up window which is Sort Warning.
- Then, select Expand the selection in the popped-out prompt box.
- Now, click on the OK button to close the pop-up window.
- This will open the Sort dialog box.
- Now, choose the helper column name we want to sort by in the Column section, and then use Cell Values in the Sort On part to select the sort Order as per our desire, So we choose the A to Z sort order.
- Finally, click the OK button.
Read More: How to Sort Data by Value in Excel
Final Output of Alphanumeric Data Sorting in Excel
Here, you can see the data has been sorted as per the requirements.
You can now, Delete the helper column if you want.
Read More: How to Undo Sort in Excel
Download Practice Workbook
You can download the workbook and practice with them.
Conclusion
The above methods will assist you in Sort Alphanumeric Data in Excel. Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section. Or you can have a glance at our other articles in the ExcelDemy blog!
This process does not work in Excel (Microsoft® Excel® for Microsoft 365 MSO, Version 2302 Build 16.0.16130.20378, 64-bit) when the data to be sorted is:
C1, C10, C100, C2, C20, C200, C3…
… and you want C1, C2, C3, C10… as the result.
Hello Jeff,
Thank you for your query. You can find the answer to your question in the Excel file linked to this message.
Sort Alphanumeric.xlsx
Follow the steps shown below to sort alphanumeric data.
Enter the formula into the E5 cell to convert the alphanumeric data to numeric data >> drag the Fill Handle tool to copy the formula to the cells below.
=VALUE(CONCATENATE(COLUMN(INDIRECT(LEFT(D6,2)&1)),RIGHT(D6,LEN(D6)-1)))Now, select the E5:E13 range >> click Sort & Filter >> select Sort Smallest to Largest.
A warning box appears, Expand the selection option is checked by default >> click the Sort button.
That’s it the data will be sorted.
Hope you find this useful. Have a good day.
Regards,
ExcelDemy.