Sorting table of numbers with different place values

[Lynn]

How can I sort cells with letters and numbers, and the numerical digits have
different place values. For instance here is a simple list without decimals,
AA21, AA100, AA1, AA2. Is there a formula or macro that will sort the cells
as AA1, AA2, AA2, AA21, AA100?

 

[Otto Moehrbach]

No. You will need to use a helper column. In that column place only the
numerical portion of the entry. You can do this with a formula. Post back
if you can furnish more detail about the entries and want help with that
formula. Having done that, you sort all the columns by that helper column.
Then you delete the helper column. HTH Otto

 

[Lynn]

Thanks for the help Otto. Here is a better example of the items that need to
be in correct order. BE4-1.0L60 should be the first item after a sort.
BE16-2.0L400
BE16-2.5L200
BE16-2.5L250
BE16-2.5L315
BE16-2.5L400
BE4-1.0L100
BE4-1.0L125
BE4-1.0L160
BE4-1.0L60
BE4-1.0L80
BE4-1.2L100
BE4-1.2L125
BE4-1.2L160

 

[Otto Moehrbach]

Lynn
If your data starts in A1, put this formula in the first row of any
column (your helper column). Modify this formula as needed to go with the
first cell of your data. Drag this formula down with the fill handle as
necessary. As written this formula will produce everything that follows the
"L" in your data. Note that the first 3 characters of the formula are Equal
Dash Dash. Obviously, this formula will not work out for you if your data
has an "L" somewhere other than immediately preceding the trailing number.
HTH Otto
=--RIGHT(A1,LEN(A1)-SEARCH("L",A1))