Others

Why Excel Sorts A10 Before A5 and How to Fix It

Steven Parker Oct 22, 2026 868 views

Question details

The user needs to understand why alphanumeric strings sort out of expected numerical sequence and how to correctly sort items like A1, A2, A5, and A10 in ascending order.

How to Fix Excel Sorting A10 Before A5 (Alphanumeric Sort Issue)

Product: Excel
Device & OS: not provided
Scenario: Sorting a dataset containing alphanumeric string values (mixed letters and numbers) in a spreadsheet column.
Observed behavior: Excel places 'A10' before 'A5' because it performs a character-by-character lexical text sort rather than evaluating the numerical value at the end of the string.

Before you start

Ensure your dataset contains a clear header row and freeze your top row so headers are not mixed into the sorting process.

Solution 1Recommended

Fix Alphanumeric Sorting using a Zero-Padded Helper Column

Create a new column using a formula to extract the text and pad the numbers with leading zeros, forcing the spreadsheet to sort them accurately.

Because alphanumeric values are treated as text, Excel sorts them left-to-right character by character. Since the character '1' in 'A10' comes before '5' in 'A5', 'A10' appears first. By padding the numbers with leading zeros (transforming 'A5' to 'A05' and 'A10' to 'A10'), the standard text sort will align with the numerical order.

Insert a blank helper column

Right-click the column letter immediately to the right of your alphanumeric data and select 'Insert' from the context menu to create a new blank column.

Enter the text formatting formula

Click the first empty cell next to your data (e.g., B2) and enter the formula: =LEFT(A2,1)&TEXT(MID(A2,2,2),"00"). This separates the first letter from the number and pads single digits with a zero.

Fill the formula down the column

Select the cell with your new formula and double-click the fill handle (the small square in the bottom-right corner of the cell) to apply it to all rows in your dataset.

Apply the sort to the helper column

Highlight your entire dataset, navigate to the 'Data' tab on the top ribbon, and click the 'Sort' button. In the dialog box, choose your new helper column under 'Sort by', select 'A to Z', and click 'OK'.

Fix Alphanumeric Sorting using a Zero-Padded Helper Column

Formula Adaptation: If your prefix contains more than one letter (e.g., AB5) or your numbers exceed two digits, adjust the character counts in the LEFT and MID functions, and add more zeros to the TEXT function (e.g., "000").

Seamless Spreadsheet Data Sorting

Sort Alphanumeric Data Effortlessly with WPS Spreadsheet

WPS Spreadsheet seamlessly handles complex text, numbers, and formulas, fully supporting standard functions like LEFT, MID, and TEXT to resolve alphanumeric sorting challenges without friction.

1. Open your data in WPS Spreadsheet: Launch WPS Office, open your spreadsheet file, and right-click to insert a new column next to your target data.
2. Apply the zero-padding formula: Type the formula =LEFT(A2,1)&TEXT(MID(A2,2,2),"00") into the new column to restructure the alphanumeric text.
3. Sort via the Data tab: Select your range, go to the 'Data' tab, click 'Sort', and choose your helper column to instantly organize your data correctly.

100% compatible with Microsoft Excel formulas and standard sorting featuresLightweight and highly responsive, even when sorting massive datasetsFree to use with a familiar interface that makes transitioning easyAdvanced custom sorting options for complex data management

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel sort numbers like 1, 10, 2 instead of 1, 2, 10?

This happens when numbers are stored as text. Excel performs a lexical sort (character by character from left to right), so the character '1' in '10' is evaluated before the character '2'. Converting the text to numbers or padding them with zeros resolves this.

Can I sort alphanumeric data without using a helper column?

For small, predictable datasets, you can use the 'Custom Sort' feature to manually define the order of items. However, for large or dynamic datasets, using a helper column with extraction formulas is the most reliable and efficient method.

What does the TEXT function do in this sorting formula?

The TEXT function takes a numerical value and converts it into text using a specific format code. In this scenario, the format code "00" forces single-digit numbers to appear with a leading zero (e.g., turning '5' into '05'), which corrects the left-to-right character sorting sequence.

Office Quick Solutions