Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts

    excel sort (2003)

    I have a series of alphanumeric codes that I would like to sort. The codes are a series of letters followed by numbers and I would like the numbers to sort in numerical order. For the example set of data:
    ab-1
    ab-2
    ab-22
    ab-222
    ab-3
    ab-4
    ac-1
    ac-11
    ac-111
    ac-2

    the alpha sort is correct, but I want the numbers in sequence. I think the best way to do this is to make all of the numbers 3 digits long; e.g,, ab-1 to ab-001.
    Since I have several thousand of these codes to correct, is there a simple way to get all of the numbers to three digits?

    thanks.

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

    Re: excel sort (2003)

    You could do the following. Let's assume the codes are in column A, starting in A1.
    Enter the following formula in B1:
    <code>
    =LEFT(A1,FIND("-",A1)-1)
    </code>
    and in C1:
    <code>
    =VALUE(MID(A1,FIND("-",A1)+1,100))
    </code>
    Fill down B1:C1 as far as needed.
    Sort on column C, then on column B.

  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: excel sort (2003)

    To convert to something like "ab-001" you could combine and modify Hans' formula to:
    =LEFT(A1,FIND("-",A1)-1)&"-"&TEXT(VALUE(MID(A1,FIND("-",A1)+1,100)),"000")

    Then copy this down the column and copy- pastespecial -value over the original values

    Steve

Posting Permissions

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