Sorting table of numbers with different place values

  • Thread starter Thread starter Lynn
  • Start date Start date
L

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?
 
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
 
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
 
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))
 
Back
Top