sorting number in ascending order
 

Hi,
I have a query result, and one of those fields are some ID numbers.
It came as ="number" format when I exported to excel.
When I tried sorting this field in ascending order, however, it didnt' sort
right.
For example, the order before sort is:

100422
100422
100459
100459
1184
1187
1189
1190
1192
1193

But apparently, I want the sorting to result like below:

1184
1187
1189
1190
1192
1193
100422
100422
100459
100459

But it didn't happen with sort. I then copied the whole column and past
specile values to get rid of ="number" format, and then resorted, and
it's still the same.

When I double clicked each cell and pressed "enter," then the sorting
happense correctly. But I can't go through thousands of rows like this.

Does any one know what's up with this?

--
Message posted via http://www.officekb.com

You need to convert the numbers from Text values to numeric values.

Select a blank cell. Copy it. Select your column of numbers. Choose
Edit/Paste Special, selecting the Add and Values radio buttons. Click OK.

That will coerce text "numbers" to numeric and you should be able to
sort.


In article ,
"Janice Lee via OfficeKB.com" wrote:

But it didn't happen with sort. I then copied the whole column and past
specile values to get rid of ="number" format, and then resorted, and
it's still the same.

When I double clicked each cell and pressed "enter," then the sorting
happense correctly. But I can't go through thousands of rows like this.

I run into this all the time. Excel thinks the numbers are text. make a new
column that is =value(a1) and copy it all the way down, then you will have
numbers, then copy / paste special / values to get rid of the formula.