1. ## 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. ## 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. ## 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
•