Sorting Titles

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

[JethroUK©]

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"



"JediLuke" <[email protected]> wrote in
message news:[email protected]...
>
> 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.
>
>
> --
> JediLuke
> ------------------------------------------------------------------------
> JediLuke's Profile:
http://www.excelforum.com/member.php...o&userid=26475
> View this thread: http://www.excelforum.com/showthread...hreadid=397443
>

[Bob Phillips]

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)


"JediLuke" <[email protected]> wrote in
message news:[email protected]...
>
> 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.
>
>
> --
> JediLuke
> ------------------------------------------------------------------------
> JediLuke's Profile:
http://www.excelforum.com/member.php...o&userid=26475
> View this thread: http://www.excelforum.com/showthread...hreadid=397443
>

[David McRitchie]

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=efJ...TNGP14.phx.gbl
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...numberstowords
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"JediLuke" <[email protected]> wrote in message
news:[email protected]...
>
> 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.
>
>
> --
> JediLuke
> ------------------------------------------------------------------------
> JediLuke's Profile: http://www.excelforum.com/member.php...o&userid=26475
> View this thread: http://www.excelforum.com/showthread...hreadid=397443
>

[JediLuke]

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

[Jim Cone]

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
[email protected]XX


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

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