Sort by number not by letter (C1, A2, B3, D4)
 

I have numbers with a letter in front. I want to sort them by the
number and ignore the letter. The only way Excel does it is by the
letter (unless I'm missing something). Here is an example of my list:

A541
A586
A600
A618
A660
B299
B327
B416
B421
B501
B547
B567
L917
M125
N207
P196
PI93
T279
T284
T312
V180
V324
X327
X341
Z315
Z318
Z340
Z372

As you can see, it sorts it by the letter first, then number. I want
it to sort by the number first, then the letter. Does anyone know how
I can do this? Thanks!


--
dukemeiser
------------------------------------------------------------------------
dukemeiser's Profile: http://www.excelforum.com/member.php...o&userid=34141
View this thread: http://www.excelforum.com/showthread...hreadid=539114

Sort by number not by letter (C1, A2, B3, D4)
 

One of your entries has two leading alphas.
That complicates things. With only one leading alpha, you can
enter the following formula in an adjoining column, fill down and
then sort both columns using the new column as the sort column.
Then clear the new column... =RIGHT(B5,LEN(B5)-1)
--
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html


"dukemeiser"
wrote in message
I have numbers with a letter in front. I want to sort them by the
number and ignore the letter. The only way Excel does it is by the
letter (unless I'm missing something). Here is an example of my list:

A541
A586
A600
A618
A660
B299
B327
B416
B421
B501
B547
B567
L917
M125
N207
P196
PI93
T279
T284
T312
V180
V324
X327
X341
Z315
Z318
Z340
Z372

As you can see, it sorts it by the letter first, then number. I want
it to sort by the number first, then the letter. Does anyone know how
I can do this? Thanks!
--
dukemeiser

Sort by number not by letter (C1, A2, B3, D4)
 

Hey, it worked! Thanks a million!


--
dukemeiser
------------------------------------------------------------------------
dukemeiser's Profile: http://www.excelforum.com/member.php...o&userid=34141
View this thread: http://www.excelforum.com/showthread...hreadid=539114

Sort by number not by letter (C1, A2, B3, D4)
 

I'm glad as the formula to handle 1 or 2 leading characters is six times longer.
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"dukemeiser"
wrote in message
Hey, it worked! Thanks a million!
--
dukemeiser