Forum Discussion

PatDor's avatar
PatDor
Copper Contributor
Jan 28, 2025

Sorting

How do I sort a text field by the first digits?  I would like it to sort these as  0031 0031A 0033 0033A.  Instead it is sorting as 0031 0033 0031A 0033A

  • insightsgeek's avatar
    insightsgeek
    Brass Contributor

    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. 😊

  • m_tarler's avatar
    m_tarler
    Bronze Contributor

    Text will be sorted as you request.  The way you don't want it to be sorted happens if 0031 and 0033 are entered as numbers (not forced to be text).  As example see below:

    column B has all entries as forced to be text and sorted as preferred but column C was not forced to be TEXT so 0031 and 0033 are numbers. 

    So during entry if you format that column as TEXT instead of GENERAL it will force those entries as TEXT.  Alternatively you can enter the values prefixed with an apostrophy (') so '0031 will be treated and shown as text of 0031.

    Lastly in column D I did use a formula to force each to be text and then sort it.

    EDIT: just realize the TEXT formula allows arrays fine so no BYROW needed:

    =SORT(TEXT(C1:C4,"0000"))

     

Resources