170b is not a number, it's text.

I would try to separate the data into 3 columns--alphabetic prefix, numeric base
and alphabetic suffix (the prefix and base could be ""). Then sort the data by
these 3 helper columns.

But the formulas in those helper columns would depend on what can be in the
original column of data.

Is it always a single alpha either at the left or right of the string--never
both, sometimes both?

You may want to post a description and a representative sample of data to get
the formulas right.

This assumes that it's @####@ or ####@ or @#####.

In B1:
=IF(ISNUMBER(A1),"",IF(ISNUMBER(-RIGHT(A1,1)),LEFT(A1,1),""))

in C1:
=IF(ISNUMBER(A1),A1,
IF(ISNUMBER(-RIGHT(A1,1)),--RIGHT(A1,LEN(A1)-1),--LEFT(A1,LEN(A1)-1)))

In D1:
=IF(ISNUMBER(A1),"",IF(ISNUMBER(-RIGHT(A1,1)),"",RIGHT(A1,1)))

And then drag down as far as the data goes.

Then sort A:D by column B, then C, then D.



Slim Slender wrote:

I think it's reasonable to want the following items sorted in the following
order:
170B
424
439A
628
731
735
D08
D15

and, in fact, that's how I need them. However, Excel insists on putting them
in this order, which is rather scrambled but somewhat logical:
424
628
731
735
170B
439A
D08
D15
It appears to be sorting numbers first and then anything that contains an
alpha (treating digits as letters). Is there a way to get the first sort
order?

--

Dave Peterson