Sorting excluding "the"
 

How do you exclude the word "the" when you want to sort a list of titles that
begin with "The". I want to put in alphabetical order, but don't know how to
sort without the "The" becoming the first word? Any help would be appreciated.

Sorting excluding "the"
 

KSE

I am not aware that is a built-in method to do this in Excel. Nonetheless,
this will work.

Suppose your titles are in column A on the spreadsheet i.e. start in A1 then
A2 etc.

In cell B1 type the follwoing formula:

=TRIM(IF(LEFT(A1,3)="The", RIGHT(A1,LEN(A1) - 3) & "," &LEFT(A1,3),A1))

Now drag it down for the all the tiels that you have. This should place the
titles in the format you want e.g.

The Terminator = Terminator, The

Then you can simply sort that column.

I hope this helps.

Alex


"KSE" wrote:

How do you exclude the word "the" when you want to sort a list of titles that
begin with "The". I want to put in alphabetical order, but don't know how to
sort without the "The" becoming the first word? Any help would be appreciated.

Sorting excluding "the"
 

Alex,

A very similar question was posted yesterday by poster "kcg727", except that
kcg727 wanted to also trim titles beginning with "A". My approach was
similar to your function except I included spaces after "The" and "A" so we
didn't end up mangling a title beginning with a word like Theater.

Steve


"Alex" wrote in message
...
KSE

I am not aware that is a built-in method to do this in Excel. Nonetheless,
this will work.

Suppose your titles are in column A on the spreadsheet i.e. start in A1
then
A2 etc.

In cell B1 type the follwoing formula:

=TRIM(IF(LEFT(A1,3)="The", RIGHT(A1,LEN(A1) - 3) & "," &LEFT(A1,3),A1))

Now drag it down for the all the tiels that you have. This should place
the
titles in the format you want e.g.

The Terminator = Terminator, The

Then you can simply sort that column.

I hope this helps.

Alex


"KSE" wrote:

How do you exclude the word "the" when you want to sort a list of titles
that
begin with "The". I want to put in alphabetical order, but don't know
how to
sort without the "The" becoming the first word? Any help would be
appreciated.