Sorting

Sorting will now work as expected. The issue happens when Excel treats 0031 and 0033 as numbers instead of text, which causes them to sort differently than you’d like. For example:

If some values are stored as numbers and others as text, Excel sorts them separately,

so 0031A might end up after 0033, which isn’t what you want.

To fix this, you can use a formula that extracts the numeric part first and sorts everything properly. The formula I used is:

=SORTBY(A1:A4, TEXT(LEFT(A1:A4, LEN(A1:A4) - IF(ISNUMBER(--RIGHT(A1:A4)), 0, 1)), "0000"))

This makes sure the numbers 0031 and 0033 are sorted first, and then their letter variations (like 0031A and 0033A) appear right after them.

If your values are mixed (some numbers, some text), it’s also a good idea to format the column as TEXT before entering data. Another trick is to enter values with an apostrophe ('0031), which forces Excel to treat them as text.

Now, everything will sort exactly how you want it! Hope that helps. 😊