Function Problems

How to Sort Alphanumeric Values Correctly in Excel

Muhammad Talha Oct 16, 2026 870 views

Question details

The user needs to sort mixed text-and-number strings (alphanumeric values) in natural numeric order rather than strictly alphabetical.

How to Sort Alphanumeric Values Correctly in Excel

Product: Microsoft Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Organizing lists containing alphanumeric codes (e.g., A1, A2, A10, AA1) where standard alphabetical sorting fails to order numbers logically.
Observed behavior: Excel sorts the values alphabetically by default, resulting in incorrect sequences like A1, A10, A11, A2 instead of natural numeric order like A1, A2, A10, A11.

Before you start

Ensure your data does not contain leading or trailing spaces, and consider making a backup copy of your original column before splitting the text and numbers to prevent accidental data loss.

Solution 1Recommended

Use Helper Columns to Split Text and Numbers

By splitting the alphabetical prefixes and the numeric values into two separate helper columns, you can perform a multi-level sort to achieve natural numeric ordering.

Excel handles alphanumeric strings as pure text, which is why it evaluates characters one by one from left to right. To fix this, you must separate the letters from the numbers so Excel can evaluate the numbers by their actual mathematical value.

Add helper columns

Insert two new columns next to your original data. Name the first one 'Prefix' and the second one 'Number'.

Separate letters and numbers

In the first row of your data, manually type the letter part into the 'Prefix' column and the number part into the 'Number' column. Select the cell below your typed prefix and press Ctrl+E to activate Flash Fill, doing the same for the number column to automatically extract the rest.

Open the Custom Sort dialog

Highlight all three columns (your original data, the Prefix column, and the Number column). Navigate to the 'Data' tab on the ribbon and click 'Sort'.

Set primary sort by prefix

In the Sort dialog box, choose to sort by 'Prefix' in the 'Sort by' dropdown menu, and set the order from A to Z.

Add secondary sort by number

Click the 'Add Level' button. Choose to sort by 'Number' in the 'Then by' dropdown, set the order from Smallest to Largest, and click OK.

Use Helper Columns to Split Text and Numbers

Pro Tip: Using Flash Fill (Ctrl+E) is the fastest way to separate letters and numbers if your data follows a consistent pattern. If Flash Fill isn't available, you can use formulas like LEFT and RIGHT or the Text to Columns wizard.

Solution 2

Use Text Extraction Formulas for Complex Patterns

If Flash Fill struggles with inconsistent data, you can use built-in Excel formulas to extract the numeric part for sorting.

Effortless Spreadsheet Management

Easily Sort Complex Data with WPS Spreadsheet

WPS Spreadsheet handles extensive data sorting tasks seamlessly. With built-in tools like Flash Fill, Custom Multi-level Sort, and advanced text functions, organizing alphanumeric codes is fast, accurate, and completely free.

1. Open your dataset: Launch WPS Spreadsheet and open the document containing your alphanumeric lists.
2. Split the data: Insert two helper columns and use the built-in Flash Fill shortcut (Ctrl+E) to quickly separate prefixes from numbers.
3. Access custom sort: Highlight your dataset, navigate to the 'Data' tab, and select 'Sort' to open the multi-level sorting dialog.
4. Apply sort parameters: Set the primary sort to your text column (A-Z) and add a secondary level for your number column (Smallest to Largest).

100% compatible with Microsoft Excel file formats (.xlsx, .xls, .csv).Intuitive Flash Fill feature to quickly split text and numbers without writing formulas.Multi-level custom sorting to arrange complex datasets exactly as needed.Lightweight software with a familiar, user-friendly interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel sort A10 before A2?

Excel treats mixed alphanumeric strings as pure text. In text sorting, characters are evaluated one by one from left to right; since the character '1' comes before '2', A10 is placed before A2 regardless of the mathematical value.

Does Flash Fill work for all alphanumeric patterns?

Flash Fill works best when there is a consistent pattern in your list (such as two letters followed by digits). If the pattern varies wildly across rows, you may need to use custom formulas or Power Query to reliably split the data.

How do I handle sorting if numbers are embedded in the middle of text?

If numbers are embedded within text (e.g., AB12CD), you will need more advanced formulas using functions like MID, MIN, and FIND, or use Power Query to extract just the numeric portion into a separate column for sorting.

Can I sort alphanumeric values without using helper columns?

In standard setups, helper columns are required. However, if you are using newer versions of Excel that support Dynamic Arrays, you can use the SORTBY function combined with text manipulation functions to sort alphanumeric data virtually without adding permanent columns.

Excel & Data Problems