Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sorting Numbers (Excel 2000)

    I'm attaching a list of part numbers that I want to sort. My problem is that I don't want the 5 digit numbers to appear at the end of the list. For instance, part number 27105 should be between 1540 and 2724, not at the end of the list. I know this is the default for sorting numbers in Excel, but is there any way this can be changed? I've tried formatting the numbers as text and that doesn't work either.
    Attached Files Attached Files

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sorting Numbers (Excel 2000)

    You had the right idea:
    select column A, Format | Cells | Number Tab | Custom | 00000
    Sort the data
    set the format back to number with zero decimal places

    HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Sorting Numbers (Excel 2000)

    I don't understand.
    In XL97 when I format as text it sorts the way you want.

    Steve

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting Numbers (Excel 2000)

    If you actually convert the cells to text, they should sort the way you want them to. However, just changing the cell format to Text will not convert the value in the cell to text, it will remain a number. Here is a way to do this:

    1- Choose an empty column (lets say column B for this discussion) and format the column with the General format.


    2- Enter the formula below in the formula below in row 1:

    <pre>=Text(A1,"0")
    </pre>


    3= Fill that formula down the column as far as the numbers in column A go.

    4- Select Column B and change the cell format to Text.

    5- Select Copy from the Edit menu to copy all of the cells in Column B.

    6- Select "Paste Special" from the Edit menu.

    7- In the Paste section of the dialog box click on Values. Then click OK.

    8- Delete Column A.

    You should now be able to sort the column and get the order you want.
    Legare Coleman

  5. #5
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting Numbers (Excel 2000)

    Sorry for the delay in responding. I've been out of town. Thanks for the suggestion, but it didn't work. It placed the "0" in front of the 4 digit numbers, so it still sorted incorrectly.

  6. #6
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting Numbers (Excel 2000)

    Didn't sort correctly in Excel 2000.

  7. #7
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting Numbers (Excel 2000)

    Bless you!! It worked. I will keep this for future reference.

Posting Permissions

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