2 Easy Steps to Sort Alphanumeric Data in Excel

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.

Contents hide

📁 Download Practice Workbook

Overview of Alphanumeric Data in Excel

Normal Sorting Versus Alphanumeric Sorting

Learn to Sort Alphanumeric Data in Excel with These 2 Suitable Steps

Step 1: Applying Formula in Helper Column

Step 2: Utilizing Helper Column to Sort Data

📄 Important Notes

📝 Takeaways from This Article

Conclusion

📁 Download Practice Workbook

Download the practice workbook below.

Sorting Alphanumeric Data in Excel.xlsx

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

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.

Step

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.