Results 1 to 2 of 2

Thread: Stupid sorting problem

  1. #1

    Stupid sorting problem



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I'm forced at work to use Excel 2003 and I have a large database of numbers in a column that I have to use to sort. Other columns will be sorted as well. The sort column contains text and numbers like : 23-A2-006 and 100-A2-007 etc. When I sort, the sorted columns start with 100-A1-011 and 15-M1-004 is far below this after the 150 prefix. The numbers should sort 12-M1-004, 13-M1-004....427-M2-007, but they don't. Can I do anything to get them to sort correctly?

    Thanks

  2. #2
    Conjurer CheshireCat's Avatar
    Join Date
    Dec 2011
    Location
    Victoria, Canada
    Posts
    120
    Articles
    0
    Excel Version
    Microsoft Excel 2013
    Hi RonN,

    Since your "numbers" contain text, that is how they are sorted. You need to make an adjustment for the missing "0"s for the single or two digit starting numbers. A simple approach would be to use a helper column with the formula:

    Code:
    =IF(LEN(A2)=8,"00"&A2,IF(LEN(A2)=9,"0"&A2,A2))
    Then sort the helper column.

Tags for this Thread

Posting Permissions

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