Sorting Spreadsheet Help
I made a excel spreadsheet listing all the movies that I own. I'm wanting to
put them in alphabetical order. I know how to sort by ascending and decending. My question is how do I sort so that A, AN, and THE are ignored. Thanks! |
Sorting Spreadsheet Help
Hi
Use a helper column where you remove A, AN and THE, using the formula below (one line), then select both columns and sort by the helper column. =IF(LEFT(A2,3)="An ",RIGHT(A2,LEN(A2)-3),IF(LEFT(A2,4)="The ",RIGHT(A2,LEN(A2)-4),IF(LEFT(A2,2)="A ",RIGHT(A2,LEN(A2)-2),A2))) Hopes this helps. .... Per "Lubslimegreen" skrev i meddelelsen ... I made a excel spreadsheet listing all the movies that I own. I'm wanting to put them in alphabetical order. I know how to sort by ascending and decending. My question is how do I sort so that A, AN, and THE are ignored. Thanks! |
Sorting Spreadsheet Help
I hope that your movie names are lying like this...
A Column Movie Names An Idea A Bag The Legend Soldier Paste the below formula in B2 cell and drag it to the remaining cells and the results will be look like this. =IF(LEFT(TRIM(A2),3)="The",MID(A2,FIND(" ",A2)+1,255),IF(LEFT(TRIM(A2),2)="An",MID(A2,FIND( " ",A2)+1,255),IF(LEFT(TRIM(A2),1)="A",MID(A2,FI ND(" ",A2)+1,255),A2))) B Column Movie Names Idea Bag Legend Soldier Now apply sort for B Column. But note that, this formula will works when there is space between the starting words like A, An & The. Change the formula cell reference of A2 to your desired cell. If this post helps, Click Yes! -------------------- (Ms-Exl-Learner) -------------------- "Lubslimegreen" wrote: I made a excel spreadsheet listing all the movies that I own. I'm wanting to put them in alphabetical order. I know how to sort by ascending and decending. My question is how do I sort so that A, AN, and THE are ignored. Thanks! |
Sorting Spreadsheet Help
Watch out, this formula is not working exactly as expected, if the title is
'Another Story', your formula will return 'Story', which is not what OP need. The formula in my direct reply to OP is returning the correct result. Regards, Per "Ms-Exl-Learner" skrev i meddelelsen ... I hope that your movie names are lying like this... A Column Movie Names An Idea A Bag The Legend Soldier Paste the below formula in B2 cell and drag it to the remaining cells and the results will be look like this. =IF(LEFT(TRIM(A2),3)="The",MID(A2,FIND(" ",A2)+1,255),IF(LEFT(TRIM(A2),2)="An",MID(A2,FIND( " ",A2)+1,255),IF(LEFT(TRIM(A2),1)="A",MID(A2,FI ND(" ",A2)+1,255),A2))) B Column Movie Names Idea Bag Legend Soldier Now apply sort for B Column. But note that, this formula will works when there is space between the starting words like A, An & The. Change the formula cell reference of A2 to your desired cell. If this post helps, Click Yes! -------------------- (Ms-Exl-Learner) -------------------- "Lubslimegreen" wrote: I made a excel spreadsheet listing all the movies that I own. I'm wanting to put them in alphabetical order. I know how to sort by ascending and decending. My question is how do I sort so that A, AN, and THE are ignored. Thanks! |
Sorting Spreadsheet Help
Try this:
=IF(OR(LEFT(A1,FIND(" ",A1&" ")-1)={"A","An","The"}),MID(A1,FIND(" ",A1&" ")+1,99),A1) "Lubslimegreen" wrote: I made a excel spreadsheet listing all the movies that I own. I'm wanting to put them in alphabetical order. I know how to sort by ascending and decending. My question is how do I sort so that A, AN, and THE are ignored. Thanks! |
All times are GMT +1. The time now is 03:11 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com