alpha numeric sort when numbers are decimals? ABC1, ABC2.3, ABC 1.

[Guest]

How do I sort alpha-numeric when the numbers are decimals. If number is ABC 2
and the ext is ABC14.0, the 14.0 comes before the 2. Excel only recognizes
the first digit and ignores deciam.

 

[Myrna Larson]

YOu have two options. One is to modify your data by adding leading zeroes to
the numeric part so all numbers have 2 (or 3) digits, i.e. ABC02.0.

The other is to use Data/Text to columns to split the "numbers" into two
cells, though you'll have problems with that if the alpha prefixes aren't all
the same length.

 

[Dave Peterson]

I could your same results for ABC2 (no space) and ABC14.0.

Excel treats your entries like text and 1 comes before 2.

I think I'd put the alphabetic part in one cell and the numeric in an adjacent
cell--then sort by both those helper columns.

If you have 3 alphabetic characters (a1:a9999)
in B1, I'd put: =left(a1,3)
in C1, I'd put =--mid(a1,4,255)

And drag down.

If the text varies, post back with more specifications. I'm sure someone will
come up with a better suggestion.

 

[Ron Rosenfeld]

How do I sort alpha-numeric when the numbers are decimals. If number is ABC 2
and the ext is ABC14.0, the 14.0 comes before the 2. Excel only recognizes
the first digit and ignores deciam.

If there are no spaces, and no more than one decimal, you could view your
strings as, for example, a base 16 number, convert it to base 10 in an adjacent
column, and then sort on that column.

If that's a method that appeals to you, I can post my BaseConversion routine
which handles decimals.


--ron