How to alphabetize a list while ignoring articles?
February 20, 2008 12:24 PM Subscribe
Can I alphabetize data in Excel (or elsewhere) while ignoring the articles "the", "a" and "an"?
I am a DJ, and I would like to have a printed-out list of all the songs I have so people can approach my DJ table, flip through the list and make requests. So far I've made up a playlist of all my songs in iTunes, exported it as a text file, and imported it into Excel as a .csv file. I only want columns for "Artist Name" and "Song Title", so I delete all the other columns. Then I sort my list alphabetically, first by Artist Name and then by Song Title.
The problem is that artist names or song titles that begin with "The", "A" or "An" get sorted under "T" or "A", and I don't want them to. For instance, I want "The Beatles" to be sorted under "B" and "A Tribe Called Quest" to be sorted under "T". I could manually go through iTunes and change all my song and artist names that begin with "the", "a" or "an", but that would take some time and I'm hoping there's a way to do this in Excel or elsewhere.
I found this web app called The Alphabetizer, but it doesn't seem to be able to ignore the definite article "the", and it counts the words "some" and "any" as indefinite articles when I don't want it to.
Any ideas? Thanks!
I am a DJ, and I would like to have a printed-out list of all the songs I have so people can approach my DJ table, flip through the list and make requests. So far I've made up a playlist of all my songs in iTunes, exported it as a text file, and imported it into Excel as a .csv file. I only want columns for "Artist Name" and "Song Title", so I delete all the other columns. Then I sort my list alphabetically, first by Artist Name and then by Song Title.
The problem is that artist names or song titles that begin with "The", "A" or "An" get sorted under "T" or "A", and I don't want them to. For instance, I want "The Beatles" to be sorted under "B" and "A Tribe Called Quest" to be sorted under "T". I could manually go through iTunes and change all my song and artist names that begin with "the", "a" or "an", but that would take some time and I'm hoping there's a way to do this in Excel or elsewhere.
I found this web app called The Alphabetizer, but it doesn't seem to be able to ignore the definite article "the", and it counts the words "some" and "any" as indefinite articles when I don't want it to.
Any ideas? Thanks!
Response by poster: Ooh, good idea, almostmanda. Not to get greedy, but I wonder if it would somehow be possible to move the removed "A" to the end of the artist name, so that I can get "A Tribe Called Quest" to become "Tribe Called Quest, A"
posted by Burns Ave. at 12:42 PM on February 20, 2008
posted by Burns Ave. at 12:42 PM on February 20, 2008
Best answer: Some other approaches...
Make another column, put a formula in it to strip off "A", "An," and "The" at the beginning of the artist name, sort on that column.
Alternatively, export the field "Sort Artist" (which automatically strips off these articles) from iTunes and put in a formula that chooses the Sort Artist if it exists or the Artist if Sort Artist is blank.
posted by kindall at 12:42 PM on February 20, 2008
Make another column, put a formula in it to strip off "A", "An," and "The" at the beginning of the artist name, sort on that column.
Alternatively, export the field "Sort Artist" (which automatically strips off these articles) from iTunes and put in a formula that chooses the Sort Artist if it exists or the Artist if Sort Artist is blank.
posted by kindall at 12:42 PM on February 20, 2008
Response by poster: Also helpful, kindall. Thank you!
posted by Burns Ave. at 12:51 PM on February 20, 2008
posted by Burns Ave. at 12:51 PM on February 20, 2008
Best answer: If you put "The" in cell A1 and a song or band name in cell a3 this formula should more or less work.
=IF(FIND($A$1,A3)=1,MID(A3,LEN($A$1)+2,LEN(A3))&", "&$A$1,A3)
Then:
1. copy the formula to the whole list
2. copy but (file | paste special | values ) the resulting transformed names back into column A
3. change "The" in A1 to "An" or whatever and repeat.
not super elegant, but it should get you there.
posted by shothotbot at 1:14 PM on February 20, 2008 [1 favorite]
=IF(FIND($A$1,A3)=1,MID(A3,LEN($A$1)+2,LEN(A3))&", "&$A$1,A3)
Then:
1. copy the formula to the whole list
2. copy but (file | paste special | values ) the resulting transformed names back into column A
3. change "The" in A1 to "An" or whatever and repeat.
not super elegant, but it should get you there.
posted by shothotbot at 1:14 PM on February 20, 2008 [1 favorite]
Best answer: Not to get greedy, but I wonder if it would somehow be possible to move the removed "A" to the end of the artist name, so that I can get "A Tribe Called Quest" to become "Tribe Called Quest, A"
Burns, this might do what you want; I only tested it a little, so do a few examples (I did check The The -- it works!). If I were you, I'd make this a second, hidden column. Then I'd sort all the data on this column. So you still see the original name, sorted, but it's sorted ignoring "A", "An", and "The". The formula assumes that your band list is in column A, starting at row 1. It should save existing capitalization as well.
=IF(LEFT(A1,2)="A ",MID(A1, 3, LEN(A1)) & ", " & LEFT(A1,1), IF(LEFT(A1,3)="An ",MID(A1, 4, LEN(A1)) & ", " & LEFT(A1,2),IF(LEFT(A1,4)="The ",MID(A1, 5, LEN(A1)) & ", " & LEFT(A1,3),A1)))
posted by inigo2 at 1:48 PM on February 20, 2008
Burns, this might do what you want; I only tested it a little, so do a few examples (I did check The The -- it works!). If I were you, I'd make this a second, hidden column. Then I'd sort all the data on this column. So you still see the original name, sorted, but it's sorted ignoring "A", "An", and "The". The formula assumes that your band list is in column A, starting at row 1. It should save existing capitalization as well.
=IF(LEFT(A1,2)="A ",MID(A1, 3, LEN(A1)) & ", " & LEFT(A1,1), IF(LEFT(A1,3)="An ",MID(A1, 4, LEN(A1)) & ", " & LEFT(A1,2),IF(LEFT(A1,4)="The ",MID(A1, 5, LEN(A1)) & ", " & LEFT(A1,3),A1)))
posted by inigo2 at 1:48 PM on February 20, 2008
Response by poster: More helpful answers. Thanks so much, folks. inigo2's monster formula works like a charm!
posted by Burns Ave. at 2:41 PM on February 20, 2008
posted by Burns Ave. at 2:41 PM on February 20, 2008
You could also use the text to columns function using a space as the split (or sort and do this just to the ones you want split). Then sort based on the new column.
posted by Octoparrot at 5:07 PM on February 20, 2008
posted by Octoparrot at 5:07 PM on February 20, 2008
Ever so slightly less monstrous, does same job:
posted by flabdablet at 9:14 PM on February 20, 2008
=IF(ISERR(SEARCH(LEFT(A1,FIND(" ",A1,1)),"A An The ")),A1,REPLACE(A1,1,FIND(" ",A1,1),"")&", "&LEFT(A1,FIND(" ",A1,1)-1))
If you're willing to devote columns to intermediate results, it gets smaller:
B1=FIND(" ",A1,1)C1=LEFT(A1,B1)D1=IF(ISERR(SEARCH(C1,"A An The ")),A1,REPLACE(A1,1,B1,"")&", "&TRIM(C1))posted by flabdablet at 9:14 PM on February 20, 2008
This thread is closed to new comments.
1. Sort by artist name in Excel.
2. Go to the "A" section, select all artist cells starting with "A" (Select one, scroll down, hold shift, and then hit the last one).
3. Do a Find and Replace. Check match capitalization. Find "A " (that's capital A, then space--leave the quotes out). Replace it with nothing.
4. Do a run through and double-check to make sure it didn't muck up any artists with the word "a" in their name.
5. Do similar with An and The.
posted by almostmanda at 12:32 PM on February 20, 2008 [1 favorite]