Error - template LAYOUT-DATA-WRAPPER not found

A configuration error was detected in the CGI script; the LAYOUT-DATA-WRAPPER template could not be found.

Error - template STYLE-SHEET not found

A configuration error was detected in the CGI script; the STYLE-SHEET template could not be found.

Error - template SUB-TOP-BANNER not found

A configuration error was detected in the CGI script; the SUB-TOP-BANNER template could not be found.

Subject:	Re: Sorting Text with MS Excel
From:	Tom Turak <[log in to unmask]>
Reply To:	PCSOFT - Personal Computer software discussion list <[log in to unmask]>
Date:	Tue, 28 Sep 2004 15:34:39 -0500
Content-Type:	text/plain
Parts/Attachments:	text/plain (31 lines)

>
>> > -----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

ATOM RSS1 RSS2

LISTSERV.ICORS.ORG