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?action=getinfo&userid=26475
View this thread: http://www.excelforum.com/showthread.php?threadid=397443
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" <JediLuke.1u1tyc...@excelforum-nospam.com> wrote in
message news:JediLuke.1u1tyc...@excelforum-nospam.com...
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" <JediLuke.1u1tyc...@excelforum-nospam.com> wrote in
message news:JediLuke.1u1tyc...@excelforum-nospam.com...
>
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
---
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" <JediLuke.1u1tyc...@excelforum-nospam.com> wrote in message
news:JediLuke.1u1tyc...@excelforum-nospam.com...
>
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
jim.c...@rcn.comXXX
"JediLuke"
<JediLuke.1u32ed...@excelforum-nospam.com>
wrote in message
news:JediLuke.1u32ed...@excelforum-nospam.com