Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Warsaw, Indiana, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel - Sorting (Excel 97 - SR2)

    I am trying to sort a list of part numbers on a worksheet that was sent to me in an Excel file from a vendor. The interesting thing is that I can sort all of the column except the one with part numbers. Does anyone know what kind of format would me from sorting this list.

    It looks like it is currently formatted as text and I have tried to change the formatting to General, as well as Number format, but I can't get it to sort. Keep in mind, I can sort all of the other columns, so I'm guessing there is something special that I have not encountered.

    Any ideas?

    Thanks for any help you can give.

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

    Re: Excel - Sorting (Excel 97 - SR2)

    Is that column protected (can you change any of the part numbers)? What happens when you try to sort on this column? Do you get an error message? Does it sort, but the order not look correct? Can you make a copy, delete any confidential data and any unnecessary data and upload a sample that shows the problem?
    Legare Coleman

  3. #3
    Lounger
    Join Date
    Jan 2001
    Location
    Warsaw, Indiana, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel - Sorting (Excel 97 - SR2)

    The column is not protected. Yes, I can change any of the part numbers, as well as copy and paste or paste special.

    When I try to sort the column the numbers are not in number order. With the column originally formatted as text, it won't doesn't sort in order, and even when I select the column and try to change it to general format, it still won't sort properly.

    These numbers are what I get when sorted as text.
    1123, 1269, 1316, 135, 13906, 8431, 8490

    I want them sorted as numbers: 135, 1123, 1269, 1316, 8431, 8490, 13906.

    When I type the numbers myself in a column formatted as general, I get what I want. But, of course, I don't want to have to type all the numbers just to get it to sort properly.

    If I've done it correctly, you should be able to look at the file I've attached.
    Attached Files Attached Files

  4. #4
    Lounger
    Join Date
    Jan 2001
    Location
    Warsaw, Indiana, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel - Sorting (Excel 97 - SR2)

    I just tied Andrew's suggestion from another thread, "Numbers vs Text" and it looks like it solved my problem.

    Thanks for you help though. If you discover why the column was so difficult, don't hesitate sharing the insight. This was so frustrating.

  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: Excel - Sorting (Excel 97 - SR2)

    I doubt if you could get an expression like 4412-21 to be regarded as a number, other that by placing a "=" sign in front of it, and then it would not return the correct value. Do you want the dash (-) to be taken into account when sorting?

    There is one number in the list which seems out of place, i.e. 39-20035. Should that number be first after th e sort ?

    Andrew C

  6. #6
    Lounger
    Join Date
    Jan 2001
    Location
    Warsaw, Indiana, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel - Sorting (Excel 97 - SR2)

    Andrew, I did a search and replace to get rid of the dashes and replace them with a decimal point.

    Thanks. Your solution worked just great!!

  7. #7
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Kent, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel - Sorting (Excel 97 - SR2)

    I've had problems with this before. It is a formatting thing. If you change the format of a cell from say text to numeric, excel doesn't seem to register the fact until you press F2 & Enter. i generally solve this problem by creating a formula in the next column which multiplies the column you desire sorted by 1.

    This type of problem frequently appears when i import data into excel from a database.

    Hope this helps.

  8. #8
    mark33
    Guest

    Re: Excel - Sorting (Excel 97 - SR2)

    Instead of using F2 or putting a formula in an adjacent column, how about a macro that converts the numbers in place? The simple macro shown below works by:

    1. You select the cells you wish to convert
    2. Invoke the macro, which will step through each cell selected and re-enter the data (equivalent of using F2).

    This is a common problem that I see with clients on the Rolls-Royce account (Derby, England), but is usually associated with data imported from Access'97. As mentioned in other replies, can also happen where an alpha character has been removed from a part number.

    *** start of macro ***
    Sub ConvertNumber()
    Dim c As Range
    For Each c In Selection()
    c = c.Value
    Next
    End Sub
    *** end of macro ***

    Hope this is helpful.

Posting Permissions

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