sorting data, ignore "the"

[ZenMasta]

Is there a way I can sort data and ignore the first word if it starts with
"The"? Basically, I have an inventory list that has a lot of book titles
that have "The" as the first word and I'd like skip the and sort by the
second word.

 

[Ron Rosenfeld]

Is there a way I can sort data and ignore the first word if it starts with
"The"? Basically, I have an inventory list that has a lot of book titles
that have "The" as the first word and I'd like skip the and sort by the
second word.

Add a helper column with a formula that eliminates "The ".

e.g.

=IF(LEFT(A1,4)="The ",MID(A1,5,255),A1)

Then sort on the helper column.

Make sure the 255 is large enough to include all of your data in the cell.
--ron

 

[ZenMasta]

Sweet, thanks for that formula!

Add a helper column with a formula that eliminates "The ".

e.g.

=IF(LEFT(A1,4)="The ",MID(A1,5,255),A1)

Then sort on the helper column.

Make sure the 255 is large enough to include all of your data in the cell.
--ron