Sorting ten's before 2

[RASI]

Hi

These are numbers I need sorted:

1
12
2
5
20
7
13
8
9

But when I press Sort I get this:

1
12
13
2
20
5
7
8
9

What I wanted was this:

1
2
5
7
8
9
12
13
20

Whats my problem?

 

[Andrew Poulsom]

That's because your entries are text. Format the cells as General and use Data|Text To Columns to convert to numbers.

 

[RASI]

Sorry. My numbers are General.

 

[Andrew Poulsom]

In that case just use Data|Text To Columns|Finish to convert them to numbers. Note that formatting only affects the appearance of data not its type.

 

[cummingsea]

Sort worked for me without using text to columns.

Excel Workbook
	A	B	C	D	E
1	Before Sort	After Sort
2			Formated as General
3	1	1	Excel 2007
4	12	2
5	2	5
6	5	7
7	20	8
8	7	9
9	13	12
10	8	13
11	9	20
12
13
Sheet1

Excel 2007

 

[Andrew Poulsom]

Sort worked for me without using text to columns.

That's because your entries are numbers rather than text. If you format a cell as General and enter a number it will be a number. If you format a cell as Text and enter a number it will be a string. Changing the format to General subsequently won't change what the cell contains (a string), only it's appearance.

 

[cummingsea]

That's because your entries are numbers rather than text. If you format a cell as General and enter a number it will be a number. If you format a cell as Text and enter a number it will be a string. Changing the format to General subsequently won't change what the cell contains (a string), only it's appearance.

OK.

 

[Zetareticuli]

That's because your entries are text. Format the cells as General and use Data|Text To Columns to convert to numbers.

I have the same problem. However, my issue cannot be solved by simply changing the number format. My numbers look like this:
0-1
0-2
0-2-1
0-2-2
0-2-2-1
0-3
0-4-1
...
1-X
2-X
etc...

Using text sorting, 0-10 will appear before 0-2. This thread will be more useful if a more complete solution is provided.

 

[shg]

You could use a UDF:

Function PadNum(sInp As String, Optional ByVal iLen As Long = 1) As String
    ' shg 2003-1115

    ' Expands numbers in a string to iLen characters for sorting; e.g.,
    '   PadNum("13A1U3", 2)    = "13A01U03"
    '   PadNum("1.2.3.15", 3)  = "001.002.003.015"

    ' Numbers are not shortened below their minimal representation:
    '   PadNum("1.123.2.3", 2) = "01.123.02.03"

    ' Returns unpadded values if iLen = 1 or omitted
    '   PadNum("01.123.02.03") = "1.123.2.3"

    ' All non-numeric characters are returned as-is

    Dim sFmt        As String
    Dim iChr        As Long
    Dim sNum        As String
    Dim sChr        As String
    Dim bNum        As Boolean

    sFmt = String(IIf(iLen < 1, 1, IIf(iLen > 15, 15, iLen)), "0")

    For iChr = 1 To Len(sInp) + 1   ' the +1 flushes a trailing number
        sChr = Mid(sInp, iChr, 1)
        If sChr Like "#" Then
            bNum = True
            sNum = sNum & sChr
        Else
            If bNum Then
                bNum = False
                PadNum = PadNum & Format(CDbl(sNum), sFmt)
                sNum = vbNullString
            End If
            PadNum = PadNum & sChr
        End If
    Next iChr
End Function

E.g.,

       ---A--- -----B----- --------C---------
   1   0-1     00-01       B1: =PadNum(A1, 2)
   2   0-2     00-02                         
   3   0-2-1   00-02-01                      
   4   0-2-2   00-02-02                      
   5   0-2-2-1 00-02-02-01                   
   6   0-3     00-03                         
   7   0-4-1   00-04-01                      
   8   ...     ...                           
   9   1-X     01-X                          
  10   2-X     02-X                          
  11   etc...  etc...

Sort by col B.

 

[maeyks]

I have a similar concern but it involves the dates

10-Apr
11-Apr
so on then
20-Apr
21-Apr