Sorting

  • Thread starter Thread starter Vernon Balbert
  • Start date Start date
V

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.
 
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
 
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
Click to expand...

Thank you muchly!
 
Back
Top