Also..."An Affair To Remember", "9 to 5".

A commercial application from yours truly...
http://www.officeletter.com/blink/specialsort.html

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"m1rr0rshade" <m1rr0rshade@discussions.microsoft.com>
wrote in message
i have been cataloguing my movie collection as it grows and learning about
excel as i go.... my initial work around for movies beginning with "the" was
to enter them as follows

Killer, The
Killer's Kiss
Killing Fields, The
Killing Zoe

that worked for a while but then i noticed when i added the move The Killing
and sorted by title i got

Killer, The
Killer's Kiss
Killing Fields, The
Killing Zoe
Killing, The

when what i wanted was

Killer, The
Killer's Kiss
Killing, The
Killing Fields, The
Killing Zoe

i know this happens because excel is sorting the coma character after a-z.
so i found a suggestion to use a helper column to remove the word "the" by
entering

A2: "The Killing"
B2: =IF(LEFT(UPPER(A2),4)="THE ",MID(A2,5,200),A2)

i was happy until i started redoing the data entry and i noticed the
following complication.... i also want

A Night to Remember
A Real Young Girl
The Killing
The Killing Fields
Killing Zoe

to sort in the following order

The Killing
The Killing Fields
Killing Zoe
A Night to Remember
A Real Young Girl

is it possible to modify or expand what i enter in the helper column in
order to remove both the word "the" and the word "a" or is that just asking
too much.

by the way, i apologize for all the killing.

thanks for your help in advance