Results 1 to 3 of 3
Thread: excel sort (2003)

20080430, 17:08 #1
 Join Date
 May 2002
 Posts
 412
 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:
ab1
ab2
ab22
ab222
ab3
ab4
ac1
ac11
ac111
ac2
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,, ab1 to ab001.
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.

20080430, 17:13 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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.

20080430, 17:36 #3
 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 "ab001" 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