sort text as numbers excell2003 to 2000

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 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
 
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
 
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
 
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
 
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
 
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 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
 
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
 
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
 
Did you reformat the cells using the technique Bob Umlas suggested?

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

Jim Cone
San Francisco, USA
 
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...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Counting rows that fit within a range 1
No blank lines in text files 1
Sorting percentages 2
Numbers to sort as text 5
sort a table that contains both numbers and blank cells 1
sorting text & numbers 2
FORMULA Sorts neg numbers as positive.. 2
Ascending Sort formula, change to neg #: descending sort.. 6

Back
Top