Sort Alphanumeric Text

Sort Cells In Excel With Text & Numbers

Excel has a problem trying to
sort
alphanumeric cells in cells by the number portion only. The reason is simply
because Excels Sort feature evaluates each cell value by reading left to right. However, we can
over-come this in a few ways with the aid of
Excel Macros
.

Fixed Length Text

If the alphanumeric cells are all a
fixed length we can simply use another column to extract out the numeric portion
of the alphanumeric text and then sort by the new column. For example, say we
can alphanumeric text in Column A like ABC196, FRH564 etc. We can
simply add the formula below to Column B.

=--RIGHT(A1,3)

OR

=--Left(A1,3) for fixed
length alphanumeric text like 196GFT

OR

=--MID(A1,5,4) for
alphanumeric text like a-bg1290rqty where you know the number Start s at
the 5th character and has 4 numbers

Then
Fill Down
as far as needed.
Then we can select Column B, copy and Edit>Paste Special - Values. Next
we sort Columns A & B by Column B and then delete Column B.

NOTE: the double negative (--)
ensures the
number returned is seen as a true number.

Sort Alphanumeric

Any Length Alphanumeric Text

A problem comes about when the
numeric portion and/or the text portion can be any length. In these cases a macro
is best. The code below should be copied to any standard Module (Insert>Module).
Then simply run the SortAlphaNumerics Procedure.

It should be noted that the
ExtractNumber Function has 2 optional arguments (Take_decimal and
Take_negative). These are
both False if omitted. See the table below to see how alphanumeric text
is treated.