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.


๐Ÿ“ 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.

how to sort alphanumeric data in excel compared to normal sorting of data


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:

how to sort alphanumeric data in excel sample dataset


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, "###")

sort alphanumeric data in excel by entering Formula in helper column

  • 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.

sort alphanumeric data in excel by inserting Formula in helper column

๐Ÿ“• Read More: 8 Ways to Arrange Numbers in Ascending Order in Excel Using Formula


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.

sort alphanumeric data with helper column 2

๐Ÿ“• 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

(Visited 52 times, 1 visits today)
Abrar

Abrar

Hello there! I am Mohammad Abrar Uddin, an engineering graduate from BUET. I write articles and blogs related to Excel in this site and hope that the viewers can learn simple to complex solutions of Excel problems easily by reading such articles.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo