Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sort combination of letters and numbers
I have this type of data to sort: C-1, C-2, C-3, C-4, C-5, C-6, all the way
up to C-500, but it doesn't sort into number order. How can I make it sort by number, and disregard the "C-" part? thanks! |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sort combination of letters and numbers
Assuming your data is in column C and it starts in row 1... In a blank column next to your data enter this formula... =RIGHT(C1,LEN(C1)-2) Fill the formula down to the bottom of your data. Select both columns and sort using the new column as the key. -- Jim Cone Portland, Oregon USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins - try the "Special Sort" free trial - no registration) "Millabird" wrote in message I have this type of data to sort: C-1, C-2, C-3, C-4, C-5, C-6, all the way up to C-500, but it doesn't sort into number order. How can I make it sort by number, and disregard the "C-" part? thanks! |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sort combination of letters and numbers
You will need a 'helper' column with the number extracted
If the C-nnnn is in A1, use =--MID(A1,3,255) to get nnn best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Millabird" wrote in message ... I have this type of data to sort: C-1, C-2, C-3, C-4, C-5, C-6, all the way up to C-500, but it doesn't sort into number order. How can I make it sort by number, and disregard the "C-" part? thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SORT A LIST OF NUMBERS AND LETTERS A,B,C,D AND FIND MAX | Excel Worksheet Functions | |||
sort a range of numbers ignoring preceding letters | Excel Discussion (Misc queries) | |||
finding out the combination of letters! | Excel Worksheet Functions | |||
Sort mixed numbers/letters | Excel Discussion (Misc queries) | |||
How do I sort letters before numbers in Excel? | Excel Discussion (Misc queries) |