Sorting alphanumeric numbers

[Guest]

I am trying to sot several thousand items of equipment tracked by their
serial numbers. These serial numbers are items manufactured by many
different manufactures.

The problem is these serial numbers can be:
- Anywhere from one to N characters long. (Let's say ten).
- Some have leading zeros and if so, zeros these are significant
- Some serial numbers may have alpha characters embedded in the number

I am looking for some way to tell MS EXCEL, to sort these numbers from right
to left, (so two digit numbers show up before three digit numbers, etc.) And
"right hand" place holder alpha characters sorted before "left hand" place
holders.

Since some nationalities write from right to left, I would think that
someone, somewhere has run into this before.

Does anybody have any suggestions?

Thanks,
Maurice

 

[Dave Peterson]

I'm not sure I understand completely, but maybe....

Can you insert a helper column to the right of the serial numbers. Then fill
that helper column with a formula like:

=len(a2)
and drag down

Now sort by this helper column first, then your real serial number.

If this isn't close, you may want to post a sample of your before data and after
data.

===
And your serial numbers that are digits are really entered as text? If they're
really numbers just with a custom format, I think you'll need another approach.

 

[David McRitchie]

Hi Maurice,
Actually haven't seen anything like that before but Excel
would sort number differently then text so I think a
user defined function that gets the length, and the
reversed text value and a letter to make sure that
a text sort occurs for everything.

Placed "T" at beginning and end, just in case you have spaces
in your values. .text is used instead of .value because you
might have some that are all digits.

Function LTRTEXT(cell) As String
' "T" || length || reversed text || "T" -- D.McRitchie, misc, 2005-05-05
LTRTEXT = "T" & Format(Len(cell), "000") & StrReverse(cell.Text) & "T"
End Function

If you don't have StrReverse ( introduced in Excel 2000) available see
http://www.mvps.org/dmcritchie/excel/strings.htm#strreverse