Abrar
Sorting data is a widely used process that makes it easier for people to visualize and analyze data. In excel, we can easily sort data with the Sort option under the Data Menu. But we will be looking at a different type of Sorting today. In this article, we will talk about how we can sort alphanumeric data in Excel.
📁 Download Practice Workbook
Download the practice workbook below.
Overview of Alphanumeric Data in Excel
Alphanumeric data is data that involves both alphabets and numbers. When we write data that has strings containing both letters and numbers, then the string is called an alphanumeric string or data. An example of an alphanumeric data string is “16002BC”.
Normal Sorting Versus Alphanumeric Sorting
When we sort data normally, the rule is that the sorting puts the numbers first and the mixed strings are generally put behind the pure numbers. In Alphanumeric sorting, however, we convert the given data into text strings at first. Then we sort the text strings. Therefore, this type of sorting gives a different output. It can be illustrated by the following picture.
Learn to Sort Alphanumeric Data in Excel with These 2 Suitable Steps
To sort alphanumeric data like the third column of the image mentioned above, we have to follow two steps. Let us say that we have some product identification numbers with alphanumeric strings. We will use these mixed strings and sort them in the following steps. The sample dataset is as follows:
Step 1: Applying Formula in Helper Column
A Helper column will be essential to sort alphanumeric data. This step can be completed by following these steps.
- At first, we insert a helper column beside the product identification number column.
- Next, we select the first cell in the helper column, that is, cell C6, and write the following formula down in the formula bar using TEXT function.
=TEXT(B6, "###")
- This formula will convert the entries into texts. After that, we select the Fill Handle at the bottom right of cell C6 and drag it down to cell C15.
- This will convert all of our product Identification Numbers into text strings.
📕 Read More: 8 Ways to Arrange Numbers in Ascending Order in Excel Using Formula
Step 2: Utilizing Helper Column to Sort Data
Now, in the next step, we will be utilizing the Helper Column that we already created to sort the alphanumeric data. We can do this by following these substeps.
⬇️⬇️ STEPS ⬇️⬇️
- For this step, at first, we select the cell range C5:C15 and head over to Data Menu, and click on Sort.
- In the Sort by section, we select Helper Column and we select A to Z from the Order section. After that we press OK.
- A Sort warning box will appear. We select Sort numbers and numbers stored as text separately and press OK.
- The results will look like the following image.
📕 Read More: 5 Ways to Auto Sort When Data Is Entered in Excel
📄 Important Notes
🖊️ We should take care in selecting the whole dataset before clicking Sort. Otherwise, the Sort By option will not show the Helper Column option.
🖊️ The ### in the TEXT Function specifies the text format.
📝 Takeaways from This Article
You have taken the following summed-up inputs from the article:
📌 We can sort data easily by going to the Sort option under the Data Menu.
📌 To sort alphanumeric data, we have to convert all the numbers and alphabets into text strings by using a helper column at first and then apply the sort command.
Conclusion
In conclusion, I hope that this article has provided you with insights about how we can sort alphanumeric data in Excel. If you want to know more about Excel, please visit our site ExcelDen and if you have any queries regarding this topic, feel free to let us know down below in the comment section.
Related Articles
- How to Sort by Last Name in Excel
- 6 Ways to Auto Sort When Data Changes in Excel
- How to Sort Duplicates in Excel Easily
- Sort Multiple Columns Independently of Each Other in Excel
- How to Sort Alphabetically in Excel with Multiple Columns
- 4 Quick Ways to Create Custom Sort List in Excel
