Sorting Movie Titles

[NoNoBadDog]

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!

[#2]

@NoNoBadDog 

You may add helper column where remove by formula "The " and "A " from the beginning of the text and sort range by it.

[#3]

@NoNoBadDog 

=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.

[#4]

@NoNoBadDog 

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.

NikolinoDE

I know I don't know anything (Socrates)

[#5]

@NoNoBadDog 

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.