Sorting alpha numeric data

Question

I entered the above as A1 A2 A3 A4 etc but they are now showing as above. How can i get this back to its original entry sequence? They were entered as 'general'. I have tried changing the column to both text and number to sort them but the sequence remains the same. Any help would be appreciated.

Answer 1

The issue you're facing is due to Excel treating your entries as alphanumeric text instead of numbers, which leads to lexicographical (text-based) sorting instead of numerical sorting. That’s why A1, A10, A11, A12, etc. appear before A2, A3, etc.

Method 1: Use a helper column to extract numeric values

1. Insert a new column (e.g., Column D) next to your "Ref" column.
2. Use the following formula in cell D2 (assuming "Ref" values start from A2):

      =IF(ISNUMBER(VALUE(MID(A2,2,99))), VALUE(MID(A2,2,99)), 9999)
   

   • This extracts the numeric portion after the letter "A", ignoring non-standard entries.
   • If the entry is non-numeric (like "Ad"), it assigns 9999 to push it to the end.
3. Drag the formula down for all rows.
4. Sort the data using the helper column (Column D) in ascending order.

Method 2: Manual sorting using Excel's "Custom Sort"

1. Select all data (including headers).
2. Click "Sort & Filter" → "Custom Sort" (from the Data tab).
3. Choose "Ref" as the column to sort by.
4. In the Order dropdown, choose Custom List...
5. Manually enter the correct order (A1, A2, A3, A4... etc.).
6. Click OK and then Sort.

---

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin