1. ## 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.

2. ## 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.

3. ## Re: Sort (Excel 2000)

Thank you so much. I had tried everything but the "1" option. It's working fine now.

4. ## 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 12-digit numbers as text with leading zeros and all.

Thanks a lot.

Wassim

5. ## 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

6. ## 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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•