Sorting text that begins with "the" or "a"

[Guest]

I'm trying to sort a list in Excel and one of the columns is a list of
titles. When I sort I want it to ignore the words "The" and "A" at the
beginning. (for example, I want "The Piano" to be with the Ps not the Ts).

I'm about to throw my computer across the room because I can't figure it out
and can't find anything in the Help sections that gives me the right answer.
I'm not the most savvy with Excel, but this really shouldn't be that hard.
Help me.

 

[Niek Otten]

<I'm not the most savvy with Excel, but this really shouldn't be that hard.>

Well, Excel wasn't exactly made to do this kind of job, but you are right;
it can be done.
Are you sure there will not be instances where two words should be skipped?
Or "The" not skipped if the 3rd word is as special one?

Anyway, make a list of all your skip words in a column.
I had mine in H1:H3 (The, A and One) and the titles in A1:A7
In B1, I put this formula:

=IF(ISERROR(FIND(" ",A1)),A1,IF(ISNA(VLOOKUP(LEFT(A1,FIND("
",A1)-1),$H$1:$H$3,1,FALSE)),A1,RIGHT(A1,LEN(A1)-FIND(" ",A1))))

And copied it down

It skipped The, A and One, not other first words like All

Now you can sort on column B

 

[Steve Yandl]

Here is one option. Assuming the titles are in column A, create a helper
column that you will sort by and drag down the formula

=IF(LEFT(A1,2)="A ",RIGHT(A1,LEN(A1)-2),IF(LEFT(A1,4)="The
",RIGHT(A1,LEN(A1)-4),A1))

Remaining in the helper column will be the titles stripped of The{plus
space} or A{plus space} wherever they appear at the start of the title.
Then do the sort on the helper column.

Steve