Excel – sort numbers and numbers+text together in Excel

microsoft excelsorting

I am trying to sort an Excel sheet based on a column that has a mix of:

3 digit numbers (such as 404)
3 digit numbers with non-numeric characters appended (such as 802b)
non-numeric text (such as none or ?)

Excel sorts the plain numbers first, then the rest are sorted together, with the numbers+text in between punctuation marks and alpha characters.

I want the numbers and the numbers+text interleaved. But applying Format > Text to the column does nothing. How do I force it to sort numbers as text?

Best Answer

Alphabetical order: Numeric, Alphanumeric, Alpha.

1, 2, 3, 4, 5, 10, 2a, 3Regy, 4a, 6Alpha, Alpha,

If you prefer 2a to follow 2, then you need to add a space. Adding a space signifies a second grouping. Hyphenated names (words) are last.

1, 10, 2, 2 a, 2-a, 3, 3Regy, 4, 4a, 5, 6Alpha, Alpha

Further: 5 Alpha, 5Alpha, 5-Alpha, 6 Alpha, 6Alpha, Alpha, Alpha 6, Alpha5, Alpha6, Alpha-6,

Or use a Zero for place holder: 01, 02, 02a, 10

Or keep the length uniform: 001, 002, 02a, 030

Related Solutions

Excel – How to sort list-style numbers in Excel

You can make a table to split the values:

For each column, use the headings (row 1) and formulas (row 2+):

A1: text    A2: (your section numbers)
B1: dot1    B2: =FIND(".",A2,1)  
C1: dot2    C2: =IFERROR(FIND(".",A2,B2+1),LEN(A2)+1)  
D1: num1    D2: =VALUE(MID(A2,1,B2-1))  
E1: num2    E2: =IFERROR(VALUE(MID(A2,B2+1,C2-B2-1)),0)  
F1: num3    F2: =IFERROR(VALUE(MID(A2,C2+1,LEN(A2)-C2)),0)

This appears as:

      A      B      C      D      E      F

1   text   dot1   dot2   num1   num2   num3
2   1.1       2      4      1      1      0
3   1.1.3     2      4      1      1      3
4   2.10.7    2      5      2     10      7

Then you can do a custom sort by columns num1, num2 and num3.

Windows – Sort order in Windows Explorer

By default, the newer sort order considers strings in file and folder names as numeric content, not text. Numerals in folder and file names are sorted according to their numeric value.

In the following example, note how the following files, whose names contain numerals, are sorted.

Windows Vista, Windows XP, and Windows Server 2003

5.txt
11.txt
88.txt

In this example, 88 is a numerically higher value than 5. Therefore, the 88.txt is listed after the 5.txt when you sort the folders by name in ascending order.

Source: The sort order for files and folders whose names contain numerals is different in Windows Vista, Windows XP, and Windows Server 2003 than it is in Windows 2000

Best Answer

Related Solutions

Excel – How to sort list-style numbers in Excel

Windows – Sort order in Windows Explorer

Related Question