How can I alphabetize data excluding words like "a" and "the"?

I'm cataloging a list of movies, and I'd like to be able to alphabetize the
titles ignoring words like "a" and "the." Is this possible?

Create a helper column and strip leading The and A and An,

=Substitute(Substitute(Substitute(A1,"The ",""),"A ",""),"An","")

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Faith" wrote in message
...
I'm cataloging a list of movies, and I'd like to be able to alphabetize
the
titles ignoring words like "a" and "the." Is this possible?

Here's one option that might work for you. Let's say your titles are in
column A. Insert a new column B and enter this formula:

=IF(LEFT(A1,2)="a ",MID(A1,3,LEN(A1)),IF(LEFT(A1,4)="the
",MID(A1,5,LEN(A1)),A1))

Copy the formula down through column B as needed. Then, when you sort, sort
by column B. You can also hide column B if you want.

HTH,
Elkar


"Faith" wrote:

I'm cataloging a list of movies, and I'd like to be able to alphabetize the
titles ignoring words like "a" and "the." Is this possible?

This is clever.
--
Brevity is the soul of wit.


"Elkar" wrote:

Here's one option that might work for you. Let's say your titles are in
column A. Insert a new column B and enter this formula:

=IF(LEFT(A1,2)="a ",MID(A1,3,LEN(A1)),IF(LEFT(A1,4)="the
",MID(A1,5,LEN(A1)),A1))

Copy the formula down through column B as needed. Then, when you sort, sort
by column B. You can also hide column B if you want.

HTH,
Elkar


"Faith" wrote:

I'm cataloging a list of movies, and I'd like to be able to alphabetize the
titles ignoring words like "a" and "the." Is this possible?

Hello, Faith!
You wrote on Mon, 13 Nov 2006 13:28:01 -0800:

F I'm cataloging a list of movies, and I'd like to be able to
F alphabetize the titles ignoring words like "a" and "the." Is
F this possible?

You probably could do this with VBA but, for a one off sort,
you could also make a helper column with "the " and "a "
replaced by "" and sort on that. It depends, I guess, whether
you intend to do it frequently.

James Silverton
Potomac, Maryland

E-mail, with obvious alterations:
not.jim.silverton.at.comcast.not

That works, but if the first word of a movie without A, or AN or THE, starts
with any of those characters, this formula removes those first 1 - 3 letters.

IE: "Angels Among Us" in column A, becomes "gels Among Us" in the helper
column.

"Bob Phillips" wrote:

Create a helper column and strip leading The and A and An,

=Substitute(Substitute(Substitute(A1,"The ",""),"A ",""),"An","")

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Faith" wrote in message
...
I'm cataloging a list of movies, and I'd like to be able to alphabetize
the
titles ignoring words like "a" and "the." Is this possible?

No it doesn't, notice that there are spaces included

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"The ",""),"A ",""),"An ","")

So Angels will not be affected, of course if the movie's name is

"Together On A Sunday" and you are using a capital A before Sunday that will
be stripped but if that's the case one can add some more to it


=IF(LEFT(A1,2)="A ",SUBSTITUTE(A1,"A ",""),IF(LEFT(A1,3)="An
",SUBSTITUTE(A1,"An ",""),IF(LEFT(A1,4)="The ",SUBSTITUTE(A1,"The
","",1),A1)))

--


Regards,


Peo Sjoblom


"val8rie" wrote in message
...
That works, but if the first word of a movie without A, or AN or THE,
starts
with any of those characters, this formula removes those first 1 - 3
letters.

IE: "Angels Among Us" in column A, becomes "gels Among Us" in the helper
column.

"Bob Phillips" wrote:

Create a helper column and strip leading The and A and An,

=Substitute(Substitute(Substitute(A1,"The ",""),"A ",""),"An","")

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Faith" wrote in message
...
I'm cataloging a list of movies, and I'd like to be able to alphabetize
the
titles ignoring words like "a" and "the." Is this possible?