Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    May 2008
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sorting Issue (2007)

    I am having trouble with table sorting. The column I want to sort has values ranging from 030730 to BJS001. I may have value that equal 030730A. I want 030730A to fall in line directly after 030730. But, my table sorts all the number values first (030730 - 99999) then the number values that include the letters(030730A - 99999Z) then the letter values with the three digit numbers (BJS001). How do I sort it the way I want? (030730, 030730A, 99999, 99999Z, BJS001, etc.)

  2. #2
    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 Issue (2007)

    EXCEL sorts numbers before text. Format all the cells in the range to TEXT and then make sure you sort as text and not as numbers when it asks

    Steve

  3. #3
    New Lounger
    Join Date
    May 2008
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting Issue (2007)

    This didn't work. I changed all the column's cells format to text. I then clicked Sort (Numbers and Text Separately) and it is now sorting (030707A, 030707, then BJS001). Is there anyting else I can try?

  4. #4
    New Lounger
    Join Date
    May 2008
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting Issue (2007)

    I think I found a work around if not the solution. I stumbled upon this somehow...... I formated the column I wish to sort as "Number". I typed a ' before the number in the first cell. I then copied the format of this first cell in to all cells below it (this added a ' before all my cells entries). I then seleted sort, numbers and numbers stored as text separately. Doing this gave me the exact sort I was looking for. I'm not sure exactly what adding the ' before the value in the cells does, but it worked.

  5. #5
    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 Issue (2007)

    Adding a single quote should do the same thing as formatting the cells as text. It ensures that numbers are treated as text...

    Steve

  6. #6
    New Lounger
    Join Date
    May 2008
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting Issue (2007)

    Ahhh! That explains it! Thank you for your help.

Posting Permissions

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