sort text as numbers excell2003 to 2000
 

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

"jimk" wrote in message
...
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

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

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

"jimk" wrote in message
...
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

"Bob Umlas" wrote in message
...
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


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

"jimk" wrote in message
...
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

"Fredrik Wahlgren" wrote:


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

/Fredrik

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

"Fredrik Wahlgren" wrote:


"jimk" wrote in message
...
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

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

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

"Fredrik Wahlgren" wrote:


"jimk" wrote in message
...
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

"Fredrik Wahlgren" wrote:


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

/Fredrik

yes it is recorded 10/17/2004 was origional date of recording


"Fredrik Wahlgren" wrote:


"jimk" wrote in message
...
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

"Fredrik Wahlgren" wrote:


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

/Fredrik

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



"jimk" wrote in message
...
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

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

"Jim Cone" wrote:

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



"jimk" wrote in message
...
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

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

"jimk" wrote:

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

"Jim Cone" wrote:

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



"jimk" wrote in message
...
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

Did you reformat the cells using the technique Bob Umlas suggested?

Jim Cone


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


"jimk" wrote:
I removed the line of text and the macro now runs, i also re formatted the
cells to numbers however it still sorts with the zeros as high numbers


"jimk" wrote:
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


"Jim Cone" wrote:
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



"jimk" wrote in message
...
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

Yes i did and they still sort inproper


"Jim Cone" wrote:

Did you reformat the cells using the technique Bob Umlas suggested?

Jim Cone


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


"jimk" wrote:
I removed the line of text and the macro now runs, i also re formatted the
cells to numbers however it still sorts with the zeros as high numbers


"jimk" wrote:
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


"Jim Cone" wrote:
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



"jimk" wrote in message
...
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

Also I am now working in 2000

"jimk" wrote:

Yes i did and they still sort inproper


"Jim Cone" wrote:

Did you reformat the cells using the technique Bob Umlas suggested?

Jim Cone


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


"jimk" wrote:
I removed the line of text and the macro now runs, i also re formatted the
cells to numbers however it still sorts with the zeros as high numbers


"jimk" wrote:
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


"Jim Cone" wrote:
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



"jimk" wrote in message
...
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

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

Jim Cone
San Francisco, USA



"jimk" wrote in message
...
Also I am now working in 2000

"jimk" wrote:
Yes i did and they still sort inproper


"Jim Cone" wrote:
Did you reformat the cells using the technique Bob Umlas suggested?
Jim Cone


"jimk" wrote:
I removed the line of text and the macro now runs, i also re formatted the
cells to numbers however it still sorts with the zeros as high numbers


"jimk" wrote:
I removed the line of text and the macro now runs, i also re formatted the
cells to numbers however it still sorts with the zeros as high numbers


"jimk" wrote:
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


"Jim Cone" wrote:
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



"jimk" wrote in message
...
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

what are the trim and clean functions


"Jim Cone" wrote:

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

Jim Cone
San Francisco, USA



"jimk" wrote in message
...
Also I am now working in 2000

"jimk" wrote:
Yes i did and they still sort inproper


"Jim Cone" wrote:
Did you reformat the cells using the technique Bob Umlas suggested?
Jim Cone


"jimk" wrote:
I removed the line of text and the macro now runs, i also re formatted the
cells to numbers however it still sorts with the zeros as high numbers


"jimk" wrote:
I removed the line of text and the macro now runs, i also re formatted the
cells to numbers however it still sorts with the zeros as high numbers


"jimk" wrote:
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


"Jim Cone" wrote:
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



"jimk" wrote in message
...
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