sorting data, ignore "the"

  • Thread starter Thread starter ZenMasta
  • Start date Start date
Z

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

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
 
Sweet, thanks for that formula!

Ron Rosenfeld said:
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
Click to expand...
 
Back
Top