Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sorting
In Excel 2007 is there a way to sort a column and have the sort ignore
leading "the" and "a"? For example, I have a list of titles and many of them start with "The" as in "The Man Who Shot Liberty Valance" or a leading "a" as in "A View to a Kill". I'd like to sort these and have it ignore these leading words so that the first one gets sorted into the M's and the second into the V's. -- The major difference between a thing that might go wrong and a thing that cannot possibly go wrong is that when a thing that cannot possibly go wrong goes wrong it usually turns out to be impossible to get at or repair. - Douglas Adams, Mostly Harmless |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sorting
No, you would need a help column that will remove these characters
if the list starts in A2 you can use this in B2 =IF(LEFT(A2,4)="The ",SUBSTITUTE(A2,LEFT(A2,4),""),IF(LEFT(A2,2)=" A ",SUBSTITUTE(A2,LEFT(A2,2),""),A2)) copy down as long as needed and sort by the help column the above will take care of A and The -- Regards, Peo Sjoblom "Vernon Balbert" wrote in message ... In Excel 2007 is there a way to sort a column and have the sort ignore leading "the" and "a"? For example, I have a list of titles and many of them start with "The" as in "The Man Who Shot Liberty Valance" or a leading "a" as in "A View to a Kill". I'd like to sort these and have it ignore these leading words so that the first one gets sorted into the M's and the second into the V's. -- The major difference between a thing that might go wrong and a thing that cannot possibly go wrong is that when a thing that cannot possibly go wrong goes wrong it usually turns out to be impossible to get at or repair. - Douglas Adams, Mostly Harmless |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sorting
On 11/8/2007 9:19 AM, Peo Sjoblom went clickity clack on the keyboard
and produced this interesting bit of text: No, you would need a help column that will remove these characters if the list starts in A2 you can use this in B2 =IF(LEFT(A2,4)="The ",SUBSTITUTE(A2,LEFT(A2,4),""),IF(LEFT(A2,2)=" A ",SUBSTITUTE(A2,LEFT(A2,2),""),A2)) copy down as long as needed and sort by the help column the above will take care of A and The Thank you muchly! -- You, you, and you panic. The rest of you, come with me. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting VLookup vs Sorting SumProduct | Excel Discussion (Misc queries) | |||
more sorting | Excel Worksheet Functions | |||
Sorting: Sorting by the First Character | Excel Discussion (Misc queries) | |||
Sorting not getting what I want | Excel Worksheet Functions | |||
Sorting | Excel Worksheet Functions |