Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Jan 2004
    Location
    Sanford, Maine, USA
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Creative Sorting (Excel 97 & higher)

    Am wondering if there is a way to sort product codes that have differing formats. ie some will be 12345-1, others 12345 or 12345-01. Is there a way to tell Excel to sort numerically, but look at them all as being xxxxx-xx even if there are no numbers or only 1 number after a dash? Ultimately, we'd like the listing to look like the following example:
    12345
    12345-01
    12345-1
    12345-2
    12346-04
    12346-1
    and so on.

    We can't just add -00 to the numbers because they are very specific and doing so would change the item being referenced. It's quite a conundrum!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Creative Sorting (Excel 97 & higher)

    Say that your product codes are in A1:A100 (or similar).
    In B1, enter this formula:
    <code>
    =IF(ISERROR(SEARCH("-",A1)),A1&"-00",LEFT(A1,SEARCH("-",A1))&TEXT(MID(A1,SEARCH("-",A1)+1,100),"00"))
    </code>
    and fill down as far as the data go.
    Click in B1 and sort ascending. Column A will be sorted with it.

Posting Permissions

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