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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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)
 
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
 
This is clever.
--
Brevity is the soul of wit.


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