In this post we will see how we could sort values in excel with numbers in end or how to sort alphanumeric values that have numbers at the end.
The challenge associated with sorting the values with numbers in end is they will be sorted on the basis first occurrence of number e.g. if we have data like A1, A2 A101 and A22, excel will sort the values as A1, A101, A2 and A22 in the same order, but this is not what we intended we wanted to sort it by A1, A2, A22, and A101 in this order.
Below is a sample data of values/alphanumeric values that have numbers at the end on which we will perform sort by ascending order to show the challenge that we talked about earlier.
Let’s sort these alphanumeric values in ascending order.
Select the entire column in which we have the values with numbers at the end and go to sort under Data tab as shown in pic below.
After sorting these alphanumeric values, data will look like below whereas this is not exactly what we intended, we wanted the sorted data to look like A1, A3, A3, A22 and so on.
We will see how we could sort these values with numbers at the end with the help of a helper column.
Step 1
Create a column called length in which we calculate the length of each of the alphanumeric values, enter the formula =LEN(A2) in the cell B2 to calculate the length of these alphanumeric values.
Now drag the formula to accommodate all the data that you have in column A.
Step 2
Now select the entire data that you have i.e. A1:B12 and click Sort option in the Data tab.
Step 3
The idea is to sort by two column first by the length column that we have just created and then by Data column, both by ascending order.
To add another column, click Add Level as shown in the pic below.
To sort first by Length column, just change the first column to Length by drop down as shown below.
Now press OK.
As you could see our data is sorted these values with numbers at the end exactly the way we wanted.
Now you could delete or hide the helper column Length that we had created based on your requirement. If you need to sort it very frequently with new data keeps adding on, then I suggest you hide this column for future purpose.
Hope this helped.
