Excel can't sort numbers?

[NTOldTimer]

Here is a simple set of numbers that Bill apparently can't understand how to sort. I've tried to make the cells format general and text. With preceeding zeros and without. For some reason the numbers with preceeding zeros are put toward the bottom and the number 13 likes to be towards the bottom as well. What is the simple secret that I'm missing? No macros involved and running Excel 2000 (9.0.6926 SP-3)
Windows 2000 pro.

10
11
14
16
18
20
22
24
26
30
32
36
40
43
44
45
46
47
01
02
04
06
07
09
13
48
49

 

[sawedoff]

My guess is that the ones at the bottom (from 01 on) are entered as text. The easiest fix may be to convert the others to text and sort again. Another fix would be to convert the bottom ones to numeric, and format the column to display leading zeros, and resort.

Sawedoff

 

[SkipVought]

Hi,

These numbers are TEXT.

copy and paste into a module

Sub MakeNumbers()
   For Each s In Selection
      With s
         .Value = .Value
      End With
   Next
End Sub

Select the cells containing these values

run the macro


Skip,

_{[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large!}

 

[NTOldTimer]

Thanks to Skip and Sawedoff! Turns out that this spreadsheed must have been corrupted somehow. I re-typed the whole thing and it works fine now. Really strange how it chose to sort as I changed the values. But I grabbed your macro Skip and I'll play around with it. I'm trying to learn about macro's some and I appreciate your post.

 

[sawedoff]

Probably what happened is that when you re-entered them, it caused them all to be the same format (either text of numeric) based on the column default. That would then cause them to sort properly.

Sawedoff

 

[KenWright]

Just formatting the cells will NOT change the format that the existing data within the cells is treated as. You need to either re-enter the data, or use a piece of code such as the one that Skip gave you which effectively does the equivalent of re-entering it all for you.

Only once it has been re-entered will the data within the cell then take on the characteristics or format of the cell itself.

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------