thanks ron... i downloaded morefunc.xll and used the formula you suggested.
it does exactly what i was looking for... thanks

"Ron Rosenfeld" wrote:

On Sat, 11 Mar 2006 11:40:23 -0800, m1rr0rshade
wrote:

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

Here's a non-commercial solution.

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

To set up a helper column, where "the", "a", or any other First Word is
eliminated, you can use the formula:

=REGEX.SUBSTITUTE(A1,"^(A|The)\s",,,,FALSE,TRUE)

The Regular Expression "^(A|The)\s" is looking to match the pipe-separated
list of phrases that are within the parentheses, and replace it with nothing.

So you could, for example, expand it as desired:

"^(A|The|An)\s"

If you wanted to have a range that would dynamically sort, you could have a
data entry column named "rng" where you just enter the film names sequentially.
"rng" cannot refer to an entire column.

In your display column, enter the formula:

=INDEX(VSORT(rng,REGEX.SUBSTITUTE(rng,"^(A|The)\s" ,,,,
FALSE,TRUE),1),ROWS($1:1)+COUNTBLANK(rng))

and copy/drag down. This column will display the entries in "rng" in sorted
order.

This formula will give a #REF! error if you have more formula rows than
entries. This latter can be taken care of either in the formula itself, or by
using conditional formatting to check for the #REF! error and change the font
to the background color if it is present.

The dynamically sorted solution can take a long time depending on how large
"rng" is.
--ron