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.

**Table of Contents**hide

## 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)`

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

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

**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, applied

**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))`

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

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

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

**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)`

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

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

## 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**.