Quizzes & Puzzles3 mins ago
excel data sort question
1 Answers
is it possible when sorting data alphabetically, to tell it to ignore the word 'the'? so for example, if i had a title 'the usual suspects', it would sort that title under 'u' instead of 't'. i would like to do this so i don't have to write 'usual suspects, the', but i can find no options to implement this. thanks.
Answers
Only 1 answer
Best Answer
No best answer has yet been selected by shortround. Once a best answer has been selected, it will be shown here.
For more on marking an answer as the "Best Answer", please visit our FAQ.
Yes you can, but what you have to have is an additional column with a formula which will exclude the article ('the', 'a' or 'an'). So if the first line of your data is in a1, create a formula in b1 as follows:
=IF(LEFT(A1,2)="A ",RIGHT(A1,LEN(A1)-2),IF(LEFT(A1,3)= "An ",RIGHT(A1,LEN(A1)-3),IF(LEFT(A1,4)="The ",RIGHT(A1, LEN(A1)-4),A1)))
Copy the formula down so that all your data is converted, and then you can hide column B... now when you select your data (be sure to select column B as well), just sort in order of column B and not column A.
Hope this helps!
=IF(LEFT(A1,2)="A ",RIGHT(A1,LEN(A1)-2),IF(LEFT(A1,3)= "An ",RIGHT(A1,LEN(A1)-3),IF(LEFT(A1,4)="The ",RIGHT(A1, LEN(A1)-4),A1)))
Copy the formula down so that all your data is converted, and then you can hide column B... now when you select your data (be sure to select column B as well), just sort in order of column B and not column A.
Hope this helps!
Only 1 answer
