How to Sort Numbers with Letter Suffix in Excel (4 Useful Methods)

Get FREE Advanced Excel Exercises with Solutions!

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 learn how to sort numbers with letter suffix in Excel by applying 4 useful methods.


How to Sort Numbers with Letter Suffix in Excel (4 Useful Methods)

To demonstrate the process, here is a sample dataset. This dataset shows information on 6 types of Product Names and Product IDs.

Excel Sort Numbers with Letter Suffix


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.

Apply Excel VBA to Extract & Sort Numbers with Suffix Letter

  • 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

Apply Excel VBA to Extract & Sort Numbers with Suffix Letter

  • Then, save the code and close the window.
  • Next, type this formula in Cell D5.
=NUMBER(C5)

Apply Excel VBA to Extract & Sort Numbers with Suffix Letter

Note: Here, we created the NUMBER function manually with the VBA code.
  • 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.

Apply Excel VBA to Extract & Sort Numbers with Suffix Letter

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

Excel Sort Numbers with Letter Suffix

Read More: How to Put Numbers in Numerical Order in Excel


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.

Insert Helper Column to Sort Numbers with Letter Suffix

  • Then, insert this formula in Cell D5.
=VALUE(LEFT(C5,FIND("-",C5)-1))

Insert Helper Column to Sort Numbers with Letter Suffix

  • Following, press Enter > AutoFill to extract all the numbers at once.

In this formula, we used the VALUE function to return the output as a number. Then, used the LEFT function to get the left-most value from Cell C5. Afterward, the FIND function to search the value before the delimiter. Here the delimiter Dash () is placed inside Quotation Marks (“-”). Along with it, -1 helps to remove the delimiter after the numeric value.
  • Next, type this formula in Cell E5.
=RIGHT(C5,LEN(C5)-FIND("-",C5))

Insert Helper Column to Sort Numbers with Letter Suffix

  • Accordingly, press Enter > AutoFill again to pull out the suffix letters.

Here, the RIGHT function returns the right-most value from Cell C5. Then, the LEN function determines the length of the text string. Lastly, the FIND function searches for the text after the delimiter.
  • 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.

Insert Helper Column to Sort Numbers with Letter Suffix

  • Lastly, press OK.
  • Finally, you will get the sorted dataset based on the helper columns.

Read More: How to Arrange Numbers in Ascending Order in Excel Using Formula


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)

Create Dynamic Array to Sort Numbers with Letter Suffix in Excel

Here, the SORT function arranges the values of the Cell range B5:B10 in ascending order.
  • Then, insert this formula in Cell D5.
=IF(ISTEXT(C5:C10),VALUE(LEFT(C5:C10,LEN(C5:C10)-1)),C5:C10)

Create Dynamic Array to Sort Numbers with Letter Suffix in Excel

  • Afterward, press Enter to get the extracted numbers like this.

In this formula, the LEFT function extracts the left value from the Cell range C5:C10 and the LEN function determines its length. Then, the VALUE function returns the output as a numeric value. Following the ISTEXT function determines if the output is a text or not based on the condition of the IF function.
  • Next, apply this formula in Cell E5.
=RANK.EQ(D5,D$5#,1)

  • After this, press Enter to determine the order of the numbers.

The RANK function returns the rank of the numbers in Cell D5. As the value in Cell D5 is generated through the dynamic array formula, therefore the order is determined as 1.
  • Lastly, type this formula in Cell F5 and press Enter to get the final output.
=SORTBY(C5#,E5:E10,1)

Create Dynamic Array to Sort Numbers with Letter Suffix in Excel

Note: If you are using an older version of Excel then, after typing an array formula, press Ctrl + Shift + Enter to get the output.

Read More: How to Sort in Excel by Number of Characters


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

Use TEXT Function to Extract & Sort Numbers with Suffix

  • Then, hit Enter and then apply AutoFill to get all the values at once.

Here, we used the TEXT function to pull out the values in text format.
  • 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.

Read More: How to Sort Numbers in Excel


Download Practice Workbook

Get the sample file to try the methods by yourself.


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.


What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sanjida Mehrun Guria
Sanjida Mehrun Guria

Hello! Welcome to my blog. I have completed my Bachelor in Architecture from Ashanullah University of Science & Technology, Dhaka. I am a passionate, goal-oriented person with an immense thirst for knowledge and an attitude to grow continuously. Besides Architectural work, I find it very enthusiastic to work in Excel blogging. Exceldemy is a platform where I have got the opportunity to flourish my skill in Microsoft Excel and therefore, here I will be posting blogs related to it.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo