Sanjida Mehrun Guria
In our regular life, we often notice numbers with suffix letters such as product code, zip code, account code, insurance numbers and lots more. But the organizations that handle these databases, sometimes get into trouble for such a long list of numbers without correct order. In this case, Microsoft Excel is a powerful tool for sorting it. In this article, we will lurch how to sort numbers with letter suffix in excel by applying 4 useful methods.
Download Practice Workbook
Get the sample file to try the methods by yourself.
4 Useful Methods to Sort Numbers with Letter Suffix in Excel
To demonstrate the process, here is a sample dataset. This dataset shows information on 6 types of Product Names and Product IDs.
Now, let us sort these numbers with their suffixes in the correct order following the methods below.
1. Apply Excel VBA to Extract & Sort Numbers with Suffix Letter
VBA Macro code is the programming language of Microsoft Excel. It is handy for sorting the numbers along with their suffix letters. Let’s see how it works.
- First, go to the Developer tab and select Visual Basic under the Code group.
- Then, select Module from the Insert tab in the Microsoft Visual Basics for Application window.
- Now, insert this code in the blank page.
Function NUMBER(rg As Range) As String
Dim i As Integer
For i = 1 To Len(rg)
If Mid(rg, i, 1) Like "[0-9]" Then
NUMBER = NUMBER & Mid(rg, i, 1)
End If
Next i
End Function
- Then, save the code and close the window.
- Next, type this formula in Cell D5.
=NUMBER(C5)
- Afterward, press Enter > AutoFill to get all the extracted numbers as text in the Cell range D5:D10.
- After this, copy the Cell range by pressing Ctrl + C on your keyboard.
- Following this, right-click on Cell D5 and select Values from the Paste Options.
- Then, select Convert to Number in the error message Context List.
- So far, we have done the extraction part of the method.
- Now, to sort the numbers, go to the Home tab and select Sort & Filter under the Editing group.
- Here, choose any options to sort from the drop-down list.
- Lastly, select Expand the selection in the Sort Warning dialogue box and press Sort.
- Finally, we will get the sorted numbers with their letter suffix as follows.
Read More: How to Add Suffix in Excel (4 Easy Ways)
2. Insert Helper Column to Sort Numbers with Letter Suffix
We can distinguish numbers and suffixes in the correct order by inserting helper columns as well. To do this, follow the process carefully.
- First, create 2 new columns beside the dataset titled Number and Suffix.
- We also typed the Product IDs with Dash (–) between the numbers and suffixes to make it more dynamic.
- Then, insert this formula in Cell D5.
=VALUE(LEFT(C5,FIND("-",C5)-1))
- Following, press Enter > AutoFill to extract all the numbers at once.
- Next, type this formula in Cell E5.
=RIGHT(C5,LEN(C5)-FIND("-",C5))
- Accordingly, press Enter > AutoFill again to pull out the suffix letters.
- Now, go to the Data tab and select Sort.
- As a result, you will get the Sort dialogue box.
- Here, select Number and Smallest to Largest as the first level to Sort by.
- Then, press Add Level to create another row below.
- In the new level, choose Suffix and A to Z in Then by level.
- Lastly, press OK.
- Finally, you will get the sorted dataset based on the helper columns.
Read More: How to Add Text Suffix with Custom Format in Excel (5 Examples)
3. Create Dynamic Array to Sort Numbers with Letter Suffix in Excel
You may find some complex combinations of numbers and suffix letters at any stage of your work. In those situations, this method of sorting by a dynamic array will be very helpful. For easy understanding, we will only take the Product ID column from the dataset. To sort them, follow the steps below.
- In the beginning, create 4 new columns beside the dataset titled Sort Column 1, Dynamic Array Formula, Rank Column and Sort Column 2.
- Then, type this formula in Cell C5 and press Enter.
=SORT(B5:B10)
- Then, insert this formula in Cell D5.
=IF(ISTEXT(C5:C10),VALUE(LEFT(C5:C10,LEN(C5:C10)-1)),C5:C10)
- Afterward, press Enter to get the extracted numbers like this.
- Next, apply this formula in Cell E5.
=RANK.EQ(D5,D$5#,1)
- After this, press Enter to determine the order of the numbers.
- Lastly, type this formula in Cell F5 and press Enter to get the final output.
=SORTBY(C5#,E5:E10,1)
Read More: How to Add Suffix Without Formula in Excel (2 Easy Methods)
4. Use TEXT Function to Extract & Sort Numbers with Suffix
In this last method, we will learn the easiest process to extract and sort numbers with suffix letters. For this, we will use the TEXT function. Let’s see how it works.
- First, insert this formula in Cell D5.
=TEXT(C5,"###")
- Then, hit Enter and then apply AutoFill to get all the values at once.
- Now, keep the selection of the Cell range D5:D10.
- Then, right-click on it and select Sort A to Z in the Sort section of the Context Menu.
- Lastly, convert the text format to numeric as described in the first method and you will get the final output like this.
Conclusion
That’s all for today. In this article, we tried to demonstrate through 4 useful methods how to sort numbers with letter suffixes in excel. Let us know if you know of any other method for this. To learn Excel more, surf through ExcelDemy.
