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

Hi,

If your Excel does not support the REGEXEXTRACT function, and you are using Excel 2021 (updated) or a newer version of Excel (such as Excel 365 or Excel 2024), then you can try the following formula:

=LET(

d, SORT(A2:A22), seq, SEQUENCE(, 10, 0),

s, SORT(HSTACK(BYROW(d, LAMBDA(a, MID(a, 1, MIN(TOROW(SEARCH(seq, a), 3) - 1)))),

BYROW(d, LAMBDA(a, MID(a, MIN(TOROW(SEARCH(seq, a), 3)), LEN(a))))), {1,2}),

CONCATENATE(TAKE(s,, 1), TAKE(s,, -1))

)

Hope it helps.

Answer 2

Hi,

In cell B2, enter this formula

=LET(r,A2:A11,SORTBY(r,REGEXEXTRACT(r,"[A-Z]+",1,1),1,1*(REGEXEXTRACT(r,"[0-9]+")),1))

Hope this helps.

Answer 3

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