Imagine a row of song titles. I want to sort them by name as most libraries would. I want the sort to ignore the first word in the text string if it is “A” , “An”, or “The”, and instead sort by the next word.
I see a way to create custom sorts for specific words, but I don’t see how to make the list be one of ignored words.
The usual technique is to add another column for ‘sorting-title’, and fill that with the data from the regular title column, except stripping off the words you don’t want.
(I recently noted that a CD was filed in my local library under “T”, for ‘The Rolling Stones’!)
As a technique, that kinda sucks, but it’s better than nothing.
Ok, since I don’t want to go through the list line by line (it’s about 4400 titles), is there some way to do a search/replace so it doesn’t strip out the words from anywhere other than the start of the string? If I search for “A<SPACE>” and replace with nothing, it will pull out every isolated a in the title, as well as a’s on the end of words.
use the LEFT & MID functions.
Basically, if the first 2 character of the title ="A ", then take the title starting at the 3rd character, otherwise take the whole title. And the same thing for "an " and "The ", all embedded in a complicated nested IF. Do it by parts, and once you have it working, copy it for all 4400 rows.
Ok, I can do that. Thanks.