Sorting Titles

  • Thread starter Thread starter JediLuke
  • Start date Start date
J

JediLuke

I am making an inventory of movies I have, and I would like the sort the
list alphabetically.

However many movie titles begin with 'a' or 'the' and I would like to
know if words like these to be ignored when sorting.

In addition, I would like to know if the sorter can treat numerical
values as letters. For example, the film '28 Days Later' would be filed
as if it were 'Twenty-Eight Days Later.'

This is a simple single column list, I would only like titles to be
sorted as if it were a library catalogue. If anyone knows the solution
to my request, I would appreciate a response. Thanks.
 
couple of filing tips:

either exclude the preceding "The" or the "A" in the title (e.g. "The
Haunting" becomes "Haunting"), or use the filing method ("The Haunting"
becomes "Haunting, The"
 
Firstly you need a helper columned, so assuming titles in A, use B

Then you can simply remove all 'A ' and 'The ' with

=SUBSTITUTE(LOWER(SUBSTITUTE(LOWER(SUBSTITUTE(LOWER(A1),"the ","")),"an
","")),"a ","")

The number is trickier. Twenty-eight will come after thirty-one, that cannot
be correct surely, and it gets worse, one hundred and twenty-eight comes
after eight! I suggest no bothering, have then isolated.

Anyway, then sort by column B.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Hi ...,
You will probably need a macro (programming newsgroup)
but you can do the first part without a macro if you only have
two words to eliminate at the beginning -- you are limited to
a nesting level of 7. see
http://google.com/groups?threadm=efJu$H#[email protected]
be sure to look at the original posting, (options, show original)
and make the window as wide as possible as the Google format
is likely to screw up formatting.

I think the second part would create as much confusion as
it might help you so might not be worth the effort.
Since there is nothing builtin to Excel,
you would have to isolate the numbers and convert them.
There are macros to expand numbers to words see links in
http://www.mvps.org/dmcritchie/excel/strings.htm#numberstowords
 
Thank you all for the suggestions. That is basically what I expected, I
was just wondering if there was a simple work around or feature already
built in. Thanks
 
JL,

My "Special Sort" Excel add-in offers nine additional sorting methods.
It includes the "xArticles" sort that excludes leading "A", "And", "The"
when sorting. It will also sort numerically, by color, by text length,
in reverse and others. It can also include borders with the sort.

I have almost completed a new option to sort leading numeric values
by their written text equivalents (while excluding articles).
For instance it will sort in the following order...

Three Brothers
The Twelve Chairs
20,000 Leagues under the sea
28 Days
2001: a space odyssey
Two-Land Blacktop

Not exactly the same as the sort in "Film & Video companion",
but very close. The updated add-in will be available as soon as
additional testing is complete.

If you want to give it a try, let me know.
It comes with a two page Word.doc install/use file and is free upon direct request.
Remove XXX from my email address.

Jim Cone
San Francisco, USA
(e-mail address removed)


"JediLuke"
<[email protected]>
wrote in message

Thank you all for the suggestions. That is basically what I expected, I
was just wondering if there was a simple work around or feature already
built in. Thanks
 
Back
Top