Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting Alpha Numeric Part 2
Here is some more info, I have items that start with 'A' and 'D' prefixs. 'A'
sorting is not a problem since the numbers only go up to A69. The 'D's however go to 200. Is there any other way to sort, without retyping everything below 100, so I don't get the result below? Thanks. A59 A60 A63 A69 D00 D01 D08 D09 D10 D100 D101 D108 D109 D11 D110 D118 D119 D12 D120 D128 D129 D13 D130 D136 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting Alpha Numeric Part 2
Hi,
If your data are in A2:A101, create a helper column B as follows: In B2, =(CODE(LEFT(A2,1))-64)*10000+MID(A2,2,255) and autofill to B101. Select both columns (A2:B101) and sort by column B ascending. This formula would work as long as the numbers following the first letter do not have more than four digits (e.g., A9999). Regards, B. R. Ramachandran "CS Project Man" wrote: Here is some more info, I have items that start with 'A' and 'D' prefixs. 'A' sorting is not a problem since the numbers only go up to A69. The 'D's however go to 200. Is there any other way to sort, without retyping everything below 100, so I don't get the result below? Thanks. A59 A60 A63 A69 D00 D01 D08 D09 D10 D100 D101 D108 D109 D11 D110 D118 D119 D12 D120 D128 D129 D13 D130 D136 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting Alpha Numeric Part 2
Hi
One way Assuming your data is in column A, use a helper column with the formula =LEFT(A1)&TEXT(MID(A1,2,LEN(A1)-1),"000") Copy down for the length of range of data in column A Sort based on the helper column. Alternatively, if you don't mind altering your column A permanently, copy the whole of the helper column, move your cursor to the first data item in column A Paste SpecialValues. You can then delete the helper column and sort by column A. Regards Roger Govier CS Project Man wrote: Here is some more info, I have items that start with 'A' and 'D' prefixs. 'A' sorting is not a problem since the numbers only go up to A69. The 'D's however go to 200. Is there any other way to sort, without retyping everything below 100, so I don't get the result below? Thanks. A59 A60 A63 A69 D00 D01 D08 D09 D10 D100 D101 D108 D109 D11 D110 D118 D119 D12 D120 D128 D129 D13 D130 D136 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting Alpha Numeric Part 2
Thanks for all the suggestions, adding the 00's after the prefix seems the
easy way, I'll just do an Edit Auto fill. "CS Project Man" wrote: Here is some more info, I have items that start with 'A' and 'D' prefixs. 'A' sorting is not a problem since the numbers only go up to A69. The 'D's however go to 200. Is there any other way to sort, without retyping everything below 100, so I don't get the result below? Thanks. A59 A60 A63 A69 D00 D01 D08 D09 D10 D100 D101 D108 D109 D11 D110 D118 D119 D12 D120 D128 D129 D13 D130 D136 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting Alpha Numeric | Excel Discussion (Misc queries) | |||
Search string for alpha or numeric | Excel Worksheet Functions | |||
The colums changed from alpha to numeric how do you make it alpha | Excel Discussion (Misc queries) | |||
Alpha & Numeric Counts in Excel | Excel Discussion (Misc queries) | |||
Summing part of an Alpha Numeric String | Excel Worksheet Functions |