Results 1 to 6 of 6
Thread: Sort (Excel 2000)

20010907, 12:57 #1
 Join Date
 Jul 2001
 Posts
 134
 Thanks
 0
 Thanked 0 Times in 0 Posts
Sort (Excel 2000)
I have a large spreadsheet with numbers in the second column ranging from 2710 to 99999. I am trying to sort the document based on that column, in ascending numerical order. However, it sorts from 2710 to 93281, then starts over again with 2715 (which should be after 2710) on down to the end of the document. Please help ASAP. Thanks so much.

20010907, 13:18 #2
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Sort (Excel 2000)
It sounds like some of the cells contain numbers and some of them contain text (that also happen to be numbers). Here is a way to fix that:
1 Select the entire column, and do a Format/Cells and format the cells as General.
2 Select an empty cell anywhere on the sheet and enter a 1.
3 Select the cell containing the 1 and do an Edit/Copy.
4 Select the column you are trying to sort on again and then do Edit/"Paste Special".
5 In the dialog box, in the "Operation" section select Multiply.
6 Click OK and try to sort again.Legare Coleman

20010907, 13:53 #3
 Join Date
 Jul 2001
 Posts
 134
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Sort (Excel 2000)
Thank you so much. I had tried everything but the "1" option. It's working fine now.

20010907, 14:18 #4
 Join Date
 Jun 2001
 Location
 New York, New York, Lebanon
 Posts
 1,449
 Thanks
 1
 Thanked 1 Time in 1 Post
Re: Sort (Excel 2000)
Legare
This is a neat trick, you multiply by one to make all "numbers" Numbers=values.
But I have some lists that need to be sorted, and the "Numbers" need to be in text numbers to preserve the leading zeros. These actually are part numbers and not numerical values, so no mathematical functions will be needed.
How can I reverse the question and make all these 12digit numbers as text with leading zeros and all.
Thanks a lot.
Wassim<img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

20010907, 14:32 #5
 Join Date
 Feb 2001
 Location
 Dublin, Ireland, Republic of
 Posts
 2,697
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Sort (Excel 2000)
You can still have leading zeros, even with the data as numeric. You just need to format it suitably.
If you want 300 to show as a six digit number you could set the format type to <big>000000</big>., and the would apply the leading zeros so as to show as 000300, but is still numeric and can be used as such in calculations and formulae.
Andrew C

20010907, 15:30 #6
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Sort (Excel 2000)
Andrew's suggestion is the best way to handle this situation. However, if you really want to convert the numeric values to 12 digit text numbers, then the following will work if the numbers are in column A:
1 Insert an empty column to the right of A and format the cells in this column as Text.
2 In the top cell of this column insert this formula:
<pre>=TEXT(A1,"000000000000")
</pre>
3 Copy this formula down to the end of the numbers in column A.
4 Select all of the cells in this column that have formulas and copy those cells.
5 Do a paste special an select Values from the Paste section of the dialog box and click on OK.
6 Delete the original column.
Column A should now have the values as 12 digit text.Legare Coleman