Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
alphanumeric sort
I would like to sort values for numbered connectors such as C1, C2, C3,
....... C200. However, excel is sorting it as C1, C100, C11, C123, etc. How do I change this??? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
alphanumeric sort
I do not think there is any direct way to do it.
You can use code to separate the text part and numerical part and then sort them. Something like this: Suppose all your values in Column A, then in Column B - extract the text part, "C" in Column C - extract the numerical part, "2", "22", etc. Sort by Column B first then Column C. -- Best regards, --- Yongjun CHEN ================================= XLDataSoft - Data Analysis Expert, Excel/VBA Specialist - - - - www.XLDataSoft.com - - - - Free Excel-Based Data Processing Tool is Available for Download Free Excel / VBA Training Materials is Available for Download ================================= "JLW" wrote in message ... I would like to sort values for numbered connectors such as C1, C2, C3, ...... C200. However, excel is sorting it as C1, C100, C11, C123, etc. How do I change this??? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
alphanumeric sort
If you always have only one "C" at the left you might want to try this array formula:
(assume your data is in A1:A6) ="C"&SMALL((RIGHT($A$1:$A$6,LEN($A$1:$A$6)-1))*1,ROW(A1)) (hold down Ctrl and Shift while pressing Enter) Regards Hans "JLW" skrev i en meddelelse ... I would like to sort values for numbered connectors such as C1, C2, C3, ...... C200. However, excel is sorting it as C1, C100, C11, C123, etc. How do I change this??? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sort alphanumeric | Excel Discussion (Misc queries) | |||
sort alphanumeric data | New Users to Excel | |||
Sort - alphanumeric. | New Users to Excel | |||
how can I sort alphanumeric entries by number in excel | Excel Worksheet Functions | |||
Alphanumeric Sort | Excel Discussion (Misc queries) |