Bad sorting

T

turnermarketing

I have a table of data. The first column is a series of numbers from
1-500 with a few numbers missing. When I sort this data ascending, it
sorts them as 1, 10, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109,
11, 110 etc. Of course what I want is 1, 2, 3, 4, etc.
What is Going on? I selected the column, went to Format>Cells>Number. I
selected "Number" with 0 decimal places.

How is this happening?

Scott
 
B

Bob Greenblatt

I have a table of data. The first column is a series of numbers from
1-500 with a few numbers missing. When I sort this data ascending, it
sorts them as 1, 10, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109,
11, 110 etc. Of course what I want is 1, 2, 3, 4, etc.
What is Going on? I selected the column, went to Format>Cells>Number. I
selected "Number" with 0 decimal places.

How is this happening?

Scott
Click to expand...
Well, the numbers are not numbers but text. The easiest way to fix this is:
1) Enter a one (1) in any spare cell.
2) copy the cell
3) select the range of numbers you want to change
4) From the Edit menu Select Paste special
5) in the operation section select multiply and then click OK

All of the text value will be converted to numbers.
 
J

Jan Putcuyps

Well, the numbers are not numbers but text. The easiest way to fix this is:
1) Enter a one (1) in any spare cell.
2) copy the cell
3) select the range of numbers you want to change
4) From the Edit menu Select Paste special
5) in the operation section select multiply and then click OK

All of the text value will be converted to numbers.
Click to expand...


I don't know if you want the missing numbers to be filled in or not. If you
want it to be exactly the same, you can use another method to convert the
text to number format by using the =value() formula in the column next to
it. This way your data will not be changed and you can use this when there
are duplicates in the column.

It has often helped me when importing text in Excel.

Jan
 
Top