Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

General discussion

Alphabetizing Titles

Oct 30, 2010 6:46AM PDT

I am trying to catalog my collection of CDS and am copying data from the Net. Titles are already alphabetized, BUT with the initial "A", "An", "And" or "The" in the correct order. A re-sort of the data will put the tiles out of accepted order. Is there a way for me to "ignore" the first word of the title in order to get a "true" alpha list of the titles?

Many thanx in advance,

rob

Discussion is locked

- Collapse -
Which application
Oct 30, 2010 6:55AM PDT

Word, Excel, Access?

I'm not sure of the problem. if titles are already in alpha order, what more do you need to do? Can you give a few examples of what you see against what you want to see?

FYI I created an Excel database, (not the best way to create a database I admit), for a friend a few years ago of his CD and record collection. We had a difference of opinion about Alpha lists. Examples below.

I said that, The Beatles, The Rolling Stones, The Cure, etc, should all be listed under T. He disagreed and said that they should be listed under B, R and C respectively, etc.

The argument he tried was Oasis. He said you wouldn't list them under The Oasis. But that didn't work for me because Oasis is Oasis, and not The Oasis. However, The Beatles are not known as Beatles, or "Beatles, The". They are known as The Beatles.

We still disagreed, so I coded it his way. He was the customer after all, but I was muttering under my breath all the way.

Mark

- Collapse -
Which App
Oct 30, 2010 9:08AM PDT

Apologies ... the app is Excel 2007.

As an example, the copied list of titles might include "A ... My Name Is Alice", "An Ordinary Man", "The Adventures of Tom Sawyer", "The Best of Broadway", "The Best Little Whorehouse in Texas", etc.

These would be listed as follows: "A ... My Name Is Alice", "The Adventures of Tom Sawyer", "The Best Little Whorehouse in Texas", "The Best of Broadway", "An Ordinary Man". As long as the list stays static, the alpha order is accurate.

If in the future, I want to add "The Story of My Life", it will be added to the bottom of the list, and then sorted with the "T" section as opposed to the "S" section. I now have a T section with 500 tiles starting with "The". Not very productive when searching.

Even though I don't want you muttering, Shocked) I tend to agree with the client re "The". When I learned alphabetizing titles, the leading article was ignored for (I believe)more accurate alphabetizing.

Many thanks again,

rob

- Collapse -
It's going to be extremely complicated in Excel
Oct 30, 2010 11:23PM PDT

Excel doesn't do database functions that well, and what you require is going to be very difficult.

I'm not sure of the "A ... My Name is Alice" example; what is the first "A", and if it is to be ignored, then wouldn't "My Name is Alice" appear under the "M"'s?

That's not important though, it just shows how different people see the problem differently, Happy

The problem is identifying whether the first word in the title is to be ignored or not, for example Mission Impossible I, II and III would all use the first word, but "The Lord of the Rings" I, II, and III you would need to identify the word "The".

I don't know how to do that in Excel. A macro would do it, but that's going to need work and knowledge, perhaps including in the macro a 'library' of short words to check the first word against. It's something I would struggle with.

Another option, you have to manually enter each title anyway, so use an extra column in Excel for the first word, but only if it is a definite or indefinite article, then sort the two columns alphabetically.

That might create it's own problems. If there is no article in the first column, will 'sort' fail? If so, use a default, nonsense word in the first column which is then hidden if it is not The, A, An, etc.

A database would probably have been much easier, Devil

Mark

- Collapse -
I have a long movie list in Excel
Oct 30, 2010 8:12AM PDT

Like 1,000 movies on DVD, VCR etc. I type the name with the article at the end like libraries do like....
Blue Brothers, The
Whole Nine Yards, The
Wizard of Oz, The

Or leave off the A, etc.

- Collapse -
Re: alfabetical order
Oct 30, 2010 11:30PM PDT

That's easy. All you have to do:
1. Add a column.
2. Copy the column with the title to that new column.
3. Edit the names in that new column such that (if they are sorted truely alfabetically) give the results you wish. So remote "The " and what more changes you wish.
4. Sort on that new column.

This is a one-time operation. If it's good, it stays good (unless you accidentally overwrite the new column. Good for a rainy afternoon.

Kees