Sort numbers

[maday]

I am trying to sort numbers like 1 to 5,000 but it sorts all the 100 and 1,000s together then the 200 with the 2,000 ect. How can I get exel to understand that 200 comes before 1000. I formated my column to be number format but this does not help. If there is a way to do this, what version are you using that gives you that option.

Thanks!

 

[tydguy17]

What could be happening is that these values are assumed to be text by excel. Did you import this from a text file? You could try selecting the column witht the numbers then going to:

Data--> Text to columns...

Step 1: Delimited
Step 2: Skip this
Step 3: Under Column Data Format select general
Finish

Then sort

Does that help?

 

[PaddyD]

hi - welcome to the boad!

Your issue is (probably) that you've got text values in there, not numbers. Changing the format won't alter this, as format's not the same thing as data type.

to change text formatted 'numbers' to proper numbers:

1) copy a blank cell, select the range of text 'numbers', go edit | paste special | add. has the effect of adding zero to the cells, & doing a math operation on the values gets excel to recognise they're numbers

2) highlight the data, go to data | text to columns. hit finish. short cuts through the text to column wizard, the point of which is to allow you set set number types / formats for text strings

 

[maday]

sorting numbers

Thank you so much PaddyD. Did what you said and it works. I knew it was picking up it as a text file but did not know how to change it to a number file. I kept going to the format cell and putting it as a number and that did not work. I did find out that in the newer version of Exel, Excel 2002 for XP does not have this problem. It sorted it without having to do any thing to the cell. Once again thank you so much.