Help with sort in Excel 2000

[TagKory]

I have a spreadsheet with a column of box numbers:

A21
A1407
A828
B7371
B986
H2490
E1082
E232
H267

When I sort the column I get:

A1407
A21
A828
B7371
B986
E1082
E232
H2490
H267

What I want is:

A21
A828
A1407
B986
B7371
E232
E1082
H267
H2490

No matter how I format the cells before I sort, I cannot get it to look like
what I want. Apparently it has something to do with the default alpha
numeric sort in excel?! Is there any way around it? Am I making sense?

 

[Trevor Shuttleworth]

Assuming that the data is in column A starting in cell A2, I suggest you
create two "helper" columns at the (right hand) end of your data, say,
columns X and Y.

In cell X2, put: =LEFT(A2,1)
In cell Y2, put =VALUE(RIGHT(A2,LEN(A2)-1))

Drag these two columns down to the end of your data. You'll get a #VALUE!
Error if you drag too far.

Now sort on columns X and Y.

Finally, delete or hide columns X and Y

Regards

Trevor

 

[Dave Peterson]

If your data really looks like one alpha then lots of numerics, I'd use two
helper columns.

=left(a1,1)
and
=--mid(a1,2,255)

and drag down.

Then sort all the data by the two helper columns.