sort text as numbers excell2003 to 2000

J

jimk

when i sort descending i get the following results, i have tried every
setting i can think of and cannot get it to sort correctly in 2000 the
workbook was created in 2003
0
0
0
87.3
87.1
75.2
67.3
54.2
 
F

Fredrik Wahlgren

jimk said:
when i sort descending i get the following results, i have tried every
setting i can think of and cannot get it to sort correctly in 2000 the
workbook was created in 2003
0
0
0
87.3
87.1
75.2
67.3
54.2
Click to expand...

When I copy these numbers into a sheet and sort them, I get

0
0
0
54.2
67.3
75.2
87.1
87.3


I think you do something wrong. You do use Data|Sort, right?

/Fredrik
 
B

Bob Umlas

The zeros may be in as text - select an unused cell (empty & formatted as
general), copy it, select the cells in question, use edit/paste special,
select Add. Sort should be OK now.

Bob Umlas
Excel MVP
 
F

Fredrik Wahlgren

Bob Umlas said:
The zeros may be in as text - select an unused cell (empty & formatted as
general), copy it, select the cells in question, use edit/paste special,
select Add. Sort should be OK now.

Bob Umlas
Excel MVP
Click to expand...

I used the wrong sort order. However, when i entered the zeros as text,
Excel asked if I wanted to treat the text as numbers. I guess jim should
have received a similar message.

/Fredrik
 
F

Fredrik Wahlgren

jimk said:
In 2003 i get that window the problem is at work we are running 2000 and it
does not ask that
My problem is that when i created the workbook the macro worked perfectly
but know it no longer runs and i can only find that it is not sorting properly

:
Click to expand...

So you use a macro? What does the code look like? Is it a recorded macro?

/Fredrik
 
J

jimk

In 2003 i get that window the problem is at work we are running 2000 and it
does not ask that
My problem is that when i created the workbook the macro worked perfectly
but know it no longer runs and i can only find that it is not sorting properly
 
J

jimk

Range("A3:M15").Select
Selection.Sort Key1:=Range("F4"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
this is the first step in the macro where it hangs up
 
J

Jim Cone

I believe that "xlSortTextAsNumbers" was added in XL2002.
Therefore, to run your sort code in XL2000 the last line
of the code should be deleted.

Jim Cone
San Francisco, USA
 
J

jimk

Thanks I will try that when i get to work later, I still wonder why it worked
when it was written on 2003 and installed on the computer that ran 2000, but
after 4 months it quit working
 
J

jimk

I removed the line of text and the macro now runs, i also re formated the
cells to numbers however it still sorts with the zeros as high numbers
 
J

Jim Cone

Use the "Trim" and "Clean" functions on the data.
Other than that, I am out of ideas.

Jim Cone
San Francisco, USA
 
J

jimk

what are the trim and clean functions


Jim Cone said:
Use the "Trim" and "Clean" functions on the data.
Other than that, I am out of ideas.

Jim Cone
San Francisco, USA
Click to expand...
 
Top