Sorting

[Vernon Balbert]

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.

 

[Peo Sjoblom]

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]

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!