Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Sorting Titles
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 |
#2
|
|||
|
|||
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" wrote in message ... 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 |
#3
|
|||
|
|||
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(LOWE R(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" wrote in message ... 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 |
#4
|
|||
|
|||
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...GP14 .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" wrote in message ... 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 |
#5
|
|||
|
|||
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 ------------------------------------------------------------------------ JediLuke's Profile: http://www.excelforum.com/member.php...o&userid=26475 View this thread: http://www.excelforum.com/showthread...hreadid=397443 |
#6
|
|||
|
|||
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 XX "JediLuke" 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 -- JediLuke |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Advice sought: Multiple column sorting | Excel Worksheet Functions | |||
Sorting movie titles? skipping The, A, An etc? | Excel Discussion (Misc queries) | |||
How to group similar column titles together???? | Excel Discussion (Misc queries) | |||
sorting number in ascending order | Excel Discussion (Misc queries) | |||
sorting question | Excel Discussion (Misc queries) |