# Open Office spreadsheet text sorting question **URL:** \ **Category:** Factual Questions\ **Created:** [February 16, 2010, 11:41pm UTC](https://boards.straightdope.com/t/open-office-spreadsheet-text-sorting-question/529266 "2010-02-16T23:41:22Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![Boyo\_Jim](https://avatars.discourse-cdn.com/v4/letter/b/87869e/32.png) [@Boyo\_Jim](https://boards.straightdope.com/u/Boyo_Jim)\ **Post date:** [February 16, 2010, 11:41pm UTC](https://boards.straightdope.com/t/open-office-spreadsheet-text-sorting-question/529266/1 "2010-02-16T23:41:22Z")
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. ---
**Author:** ![Tim\_T-Bonham.net](https://avatars.discourse-cdn.com/v4/letter/t/46a35a/32.png) [@Tim\_T-Bonham.net](https://boards.straightdope.com/u/Tim_T-Bonham.net)\ **Post date:** [February 17, 2010, 1:38am UTC](https://boards.straightdope.com/t/open-office-spreadsheet-text-sorting-question/529266/2 "2010-02-17T01:38:20Z")
> [@Boyo\_Jim](#): > > 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’!) ---
**Author:** ![Boyo\_Jim](https://avatars.discourse-cdn.com/v4/letter/b/87869e/32.png) [@Boyo\_Jim](https://boards.straightdope.com/u/Boyo_Jim)\ **Post date:** [February 17, 2010, 1:51am UTC](https://boards.straightdope.com/t/open-office-spreadsheet-text-sorting-question/529266/3 "2010-02-17T01:51:16Z")
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\** ” and replace with nothing, it will pull out every isolated a in the title, as well as a’s on the end of words. ---
**Author:** ![Tim\_T-Bonham.net](https://avatars.discourse-cdn.com/v4/letter/t/46a35a/32.png) [@Tim\_T-Bonham.net](https://boards.straightdope.com/u/Tim_T-Bonham.net)\ **Post date:** [February 17, 2010, 2:07am UTC](https://boards.straightdope.com/t/open-office-spreadsheet-text-sorting-question/529266/4 "2010-02-17T02:07:11Z")
> [@Boyo\_Jim](#): > > 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\** ” 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. ---
**Author:** ![Boyo\_Jim](https://avatars.discourse-cdn.com/v4/letter/b/87869e/32.png) [@Boyo\_Jim](https://boards.straightdope.com/u/Boyo_Jim)\ **Post date:** [February 17, 2010, 2:09am UTC](https://boards.straightdope.com/t/open-office-spreadsheet-text-sorting-question/529266/5 "2010-02-17T02:09:39Z")
Ok, I can do that. Thanks.