Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    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.

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

  3. #3
    2 Star Lounger
    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.

  4. #4
    Bronze Lounger
    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 12-digit 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>

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

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

Posting Permissions

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