Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Sorting alphanumeric numbers
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 |
#2
|
|||
|
|||
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. maurice.centner wrote: 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 |
#3
|
|||
|
|||
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...htm#strreverse --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm maurice.centner wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sorting names alphabetically when names may start with numbers | Excel Discussion (Misc queries) | |||
Sorting Numbers | Excel Discussion (Misc queries) | |||
sorting numbers more than one decimal | Excel Worksheet Functions | |||
Sorting when some numbers have a text suffix | Excel Discussion (Misc queries) | |||
Sorting imported "numbers" | Excel Discussion (Misc queries) |