How to Sort Alphanumeric Codes Like A2 and A10 in Excel

To sort alphanumeric codes in Excel so that A2 comes before A10, you have to split each code into its letters and its number, then sort by both. Excel treats a code like A10 as text and compares it one character at a time, which puts A10 ahead of A2. This guide shows three fixes, from helper columns that work in every version to a one-cell SORTBY formula, for anyone sorting bin locations, room numbers, SKUs or invoice codes.

Why Excel sorts A10 before A2#

Excel sorts text from left to right, one character at a time, like a dictionary. It compares the first characters, and only looks at the next ones if those are the same. For A10 and A2, the A matches, then the 1 in A10 beats the 2 in A2, so A10 wins. Excel never sees "ten" and "two".

Here are eight warehouse bin codes in A2:A9, sorted A to Z with the normal sort button, next to the order you actually want:

The data is fine; the sort is doing exactly what it should with text. The fix is to give Excel a real number to sort by. If a normal sort misbehaves for other reasons, such as blank rows or merged cells, see why Excel sort is not working.

Fix 1: Split the code with LEFT, MID and VALUE#

Helper columns work in every version of Excel. Add two columns next to the codes:

• LEFT(A2,1) takes the first character.
• MID(A2,2,10) starts at character 2 and takes up to 10 characters, which is "everything after the letter". MID simply stops at the end of the text if there are fewer than 10 characters.
• VALUE turns the text "10" into the number 10, so it sorts as a number.

Fill both formulas down. The LEFT, RIGHT, MID and LEN lesson covers these text functions, and the MID function guide and VALUE function guide explain each argument.

Now sort the table:

1. Select the whole table, including the headers.
2. Go to Data > Sort.
3. Sort by Letter, A to Z.
4. Click Add Level and sort by Number, Smallest to Largest.
5. Click OK.

The codes are now in natural order: A1, A2, A10, A21, B1, B2, B3, B12. Sorting by two columns this way is covered in more depth in how to sort by multiple columns.

Fix 2: Sort alphanumeric codes with one SORTBY formula#

In Excel 2021 or Microsoft 365, SORTBY can sort by the same two keys without helper columns. In an empty cell, type:

=SORTBY(A2:A9,LEFT(A2:A9,1),1,VALUE(MID(A2:A9,2,10)),1)

SORTBY takes the range to return, then pairs of "sort by this" and "which direction" (1 for ascending, -1 for descending). Here the first key is the letter and the second is the number. LEFT, MID and VALUE work on the whole range at once and pass a list of results to SORTBY.

The result spills down eight cells, in natural order. The original list stays as it is, and the sorted copy updates whenever the codes change. If the spill range is blocked, you'll see #SPILL!; clear the cells below the formula.

If every code has the same prefix, such as room numbers R1 to R12, you only need the number key: =SORTBY(A2:A7,VALUE(MID(A2:A7,2,10))). Plain SORT can't do this, because it sorts by the values themselves. See SORT and SORTBY explained and the SORT function guide for the differences, or practise SORT in a live spreadsheet.

Codes with longer or mixed prefixes#

The formulas above assume one letter at the front. Real codes are often messier, so change how you find the number:

The middle row finds the position of the first digit. FIND looks for each digit 0 to 9 in the code, MIN takes the earliest one, and adding "0123456789" to the end makes sure every digit is found somewhere, so FIND never errors. For AB12 the first digit is at position 3, so the letters are AB and the number is 12.

TEXTAFTER and TEXTBEFORE need Microsoft 365 or Excel 2024. In older versions, use =VALUE(MID(A2,FIND("-",A2)+1,10)). Our guide to extracting numbers from text has more patterns.

Fix 3: Pad the numbers with zeros#

If you control the codes, the simplest long-term fix is a fixed width: A001, A002, A010. Padded codes sort correctly with the normal sort button, because every code has the same number of characters.

To build a padded sort key from existing codes, use TEXT:

=LEFT(A2,1)&TEXT(VALUE(MID(A2,2,10)),"000")

This turns A2 into A002 and A10 into A010. Sort by this column instead of the original codes. Use enough zeros for your largest number: "000" covers up to 999.

Numbers stored as text#

Sometimes the "codes" are only numbers that were saved as text, such as IDs imported from another system. When you sort a column like that, Excel may show a Sort Warning box. Choose Sort anything that looks like a number, as a number and the column sorts correctly.

This option only helps with pure numbers. It does nothing for A10 or B12, because those are not numbers at all. For mixed codes, use one of the fixes above.

Common mistakes#

• #VALUE! in the number column. One code has no digits after the letter, or has a space. Check it, or wrap the formula with IFERROR to push it to the end.
• Hidden spaces. "A10 " with a trailing space can break FIND and VALUE. Clean the column with TRIM first.
• Sorting only one column. If you select just the codes and sort, the rest of each row stays put. Select the whole table, or use the Sort dialog, which expands the selection.
• Helper columns that point at the wrong row. Use relative references (A2, not $A$2) so each row reads its own code.

To sort by a list you define, such as High, Medium, Low, see sorting in a custom order with a formula.

Practise it#

Try the exercise on this page: sort six room codes by their number with SORTBY. Then practise the SORT function in a live spreadsheet, and practise LEFT, RIGHT, MID and LEN to get quick at pulling numbers out of codes. Each lesson checks your formula instantly.