Data Import & Export

How to Sort Alphanumeric Values Like 1a and 10a in Excel

Muhammad Talha Oct 10, 2026 873 views

Question details

The user wants to understand how Excel sorts mixed alphanumeric strings and how to arrange identifiers like 01a, 1a, and 10a into a natural numeric sequence.

How to Sort Alphanumeric Values Like 1a and 10a in Excel

Product: Excel
Device & OS: not provided
Scenario: Sorting datasets that contain identifiers, codes, or SKUs with both numbers and letters.
Observed behavior: Excel sorts text from left to right character by character, which causes numbers with more digits like 10a to appear before 2a, disrupting the natural numerical order.

Before you start

Ensure your dataset does not contain merged cells, as they will prevent sorting operations. Review your alphanumeric list to identify the exact character pattern (e.g., number followed by letter) so you can accurately separate the data.

Solution 1Recommended

Use Helper Columns to Split and Sort Alphanumeric Data

Separate the numbers from the letters into two distinct columns so Excel can evaluate them numerically first, then alphabetically.

Excel treats mixed alphanumeric entries as text, evaluating them character by character from left to right. To achieve a natural numeric sort (1, 2, 10 instead of 1, 10, 2), you must isolate the numerical part from the text part.

Create helper columns

Insert two new blank columns next to your alphanumeric data and name them 'Number Part' and 'Text Part'.

Extract the numbers and letters

Use Flash Fill by typing the numeric portion of the first cell (e.g., '1') in the 'Number Part' column and pressing Ctrl+E. Repeat this process for the 'Text Part' column by typing the letter (e.g., 'a') and pressing Ctrl+E.

Format as numbers

Select the newly extracted 'Number Part' column, navigate to the Home tab, and ensure the Number Format dropdown is set to 'Number' or 'General', clearing any text formatting.

Apply multi-level sorting

Select your entire dataset including the helper columns. Go to the Data tab and click 'Sort'. Add a primary sort level by 'Number Part' (Smallest to Largest), then click 'Add Level' and set it to sort by 'Text Part' (A to Z).

Execute the sort

Click 'OK'. Your original alphanumeric column will now be sorted in natural numerical and alphabetical order.

Use Helper Columns to Split and Sort Alphanumeric Data

Flash Fill Alternative: If Flash Fill does not recognize complex patterns, you can use Excel functions like LEFT, RIGHT, or MID combined with VALUE() to extract the numeric portions programmatically.

Solution 2

Sort Mixed Values Using Power Query

An automated method ideal for sorting large datasets, frequently updated files, or complex alphanumeric codes.

Efficient Data Management with WPS Office

Sort Complex Alphanumeric Data Easily in WPS Spreadsheet

WPS Spreadsheet provides powerful data manipulation tools, including smart Flash Fill, Text to Columns, and advanced multi-level sorting, allowing you to instantly organize mixed values like 1a and 10a with just a few clicks.

1. Open your data in WPS Spreadsheet: Launch WPS Office and open your .xlsx workbook containing the alphanumeric data.
2. Extract the numeric values: Insert a column next to your data. Type the number from the first cell, press Enter, and press Ctrl+E to activate Flash Fill to extract all numbers.
3. Sort the dataset: Highlight your data, navigate to the Data tab, and click Sort. Set your newly created number column as the primary sorting criteria from Smallest to Largest.

Free, lightweight, and fast spreadsheet applicationFully compatible with Microsoft Excel (.xlsx and .xls) formatsBuilt-in smart Flash Fill (Ctrl+E) for rapid data splittingAdvanced multi-level sorting capability for complex datasets

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel sort 10 before 2?

When numbers are stored as text or combined with letters (like '10a' and '2a'), Excel sorts them character by character from left to right. Because the character '1' comes before '2' alphabetically, '10a' will be placed before '2a' regardless of the total numerical value.

How do leading zeros affect alphanumeric sorting?

Leading zeros change the first character Excel evaluates. By padding numbers with zeros so they have the same length (e.g., '01a', '02a', '10a'), the text-based left-to-right sorting will naturally mimic a numeric sort. However, this requires modifying the original source data.

Can I use the VALUE function to sort alphanumeric strings?

The VALUE function only converts text strings that consist entirely of numbers into numeric formats. If a cell contains letters alongside numbers (e.g., '10a'), using VALUE() directly will result in a #VALUE! error. You must separate the numbers from the text before applying it.

Is it possible to sort alphanumeric data without helper columns?

Achieving natural numeric sorting for mixed strings without helper columns or Power Query is difficult using standard interface tools. In modern Excel versions, it requires highly complex array formulas (like leveraging SORTBY, SEQUENCE, and MID) or custom VBA macros.

Excel & Data Problems