>
>> > -----Original Message-----
>> > I have a text list of my DVD collection in MS Excel.
>> > I would like to sort them alphabetically - easy enough,
>> > except for movies that start with  " The " or "A"
>> >
>>snipped for brevity


The easiest method is to insert a new column for sort purposes only.  If you put this in column A, and the titles are in column B, then row 1 should be column header titles, SORT_VALUE for A and MOVIE_TITLE for B, and the dvd names start in row 2.

Then, cell A2 should have the formula

=IF(OR(LEFT(B2,2)="a ",LEFT(B2,4)="the "),MID(B2,SEARCH(" ",B2)+1,200)&", "&left(B2,SEARCH(" ",B2)-1),B2)

Copy the formula down for every row of movie titles.


OR returns true if either "a " or "the " starts the title, the test is case insensitive.  The SEARCH allows you to handle skipping two chars for "A " and 4 for "The ".

Sort the table.


Hope you haven't gone to the trouble of retyping yet!

Tom

                Curious about the people moderating your
                   messages? Visit our staff web site:
                    http://freepctech.com/staff.shtml