Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
alphanumeric sorting
Hello there,
I have this little prob in Excel: Alphanumeric Sorting is done from left to right, as default, in Excel: "Alphanumeric sort When you sort alphanumeric text, Excel sorts left to right, character by character. For example, if a cell contains the text "A100," Excel places the cell after a cell that contains the entry "A1" and before a cell that contains the entry "A11." I want to change that. Is there a way to treat the "A" as irrelevant and so that A11 actually comes between A1 And A100? Thx a bunch in advance, DK. PS: i've read the previous responses, but i thought it was too specific. I want to know if i'm missing sthing obvious here. Is there a simple command to switch the alphanumeric sorting? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
alphanumeric sorting
One work-around would be to extract the left-most character, A, then sort the
numbers, and re-concatenate the sorted strings. Example: If cells A1:A100 contain A1...A100, then in column B put the formula =RIGHT(A1,3) to extract the numbers, copy, paste special values, sort that column numerically, and then in column C: =CONCATENATE(A1,B1) and fill down. Of course there may be a more elegant way. I think you're running into a quirk in the way Windows (not Excel) sorts data. You can see the same issue in folders with dozens of files. "Dhinakaran" wrote: Hello there, I have this little prob in Excel: Alphanumeric Sorting is done from left to right, as default, in Excel: "Alphanumeric sort When you sort alphanumeric text, Excel sorts left to right, character by character. For example, if a cell contains the text "A100," Excel places the cell after a cell that contains the entry "A1" and before a cell that contains the entry "A11." I want to change that. Is there a way to treat the "A" as irrelevant and so that A11 actually comes between A1 And A100? Thx a bunch in advance, DK. PS: i've read the previous responses, but i thought it was too specific. I want to know if i'm missing sthing obvious here. Is there a simple command to switch the alphanumeric sorting? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting rows with alphanumeric starter cells | Excel Worksheet Functions | |||
Sorting alphanumeric | Excel Discussion (Misc queries) | |||
Sorting alphanumeric | Excel Discussion (Misc queries) | |||
sorting an alphanumeric list - please someone help! | Excel Discussion (Misc queries) | |||
alphanumeric sorting | Excel Discussion (Misc queries) |