Forum Discussion
Sorting Movie Titles
Hello, everyone. I am new to the forum and this is my first post. I have an excel file that lists all the movies I currently have (more than 2500 titles). All of the titles are in the first column. I would like to sort the list in such way that titles that start with "The" are filtered so that they are listed with the "the; moved to the end of the title, meaning that "The Manchurian Candidate" would become "Manchurian Candidate, The", or alternatively "Manchurian Candidate". If someone could please let me know how to do this, I will be forever in your debt. Thanks!
As variant Helper column could be minimized
formula is
=REPLACE( [@Movie], 1, 4*(LEFT( [@Movie], 4) = "The " ) + 2*( LEFT([@Movie], 2) = "A " ), "" )Here we may sort, filter, etc by this column
As variant we may minimize column with initial name and work with new column as main one.
For filtering values by first letter or last character in Excel
How to filter values by first letter or last character in Excel?
link to non-Microsoft website
Hope I was able to help you with this info.
I know I don't know anything (Socrates)
=SORTBY(C3:C8,E3:E8)You can try the above formula in cell G3 along with a helper formula:
=IF(LEFT(C3,4)="The ",TRIM(MID(C3,4,LEN(C3)-3)&", the"),C3)This formula is in cell E3 and filled down to E8 in the example.
This could be a solution if you work with Office365 or 2021 or Excel online.
You may add helper column where remove by formula "The " and "A " from the beginning of the text and sort range by it.
