Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
"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 |
#5
|
|||
|
|||
"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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
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 |
#11
|
|||
|
|||
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 |
#12
|
|||
|
|||
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 |
#13
|
|||
|
|||
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 |
#14
|
|||
|
|||
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 |
#15
|
|||
|
|||
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 |
#16
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to convert Excel imported numbers from text to numbers? | Excel Discussion (Misc queries) | |||
Numbers won't sort correctly. | Excel Discussion (Misc queries) | |||
in Excel 2000 how do I convert text to numbers? | Excel Discussion (Misc queries) | |||
Converting Numbers to Text properly | Excel Discussion (Misc queries) | |||
Sorting when some numbers have a text suffix | Excel Discussion (Misc queries) |