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? |
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? |
All times are GMT +1. The time now is 10:04 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com