How can I customize Excel's alphabetical sorting methods?

I am an editor and part of my job involves working with long lists of terms and names. We use the Chicago Manual of Style's "letter-by-letter" alphabetization method, which is one of the most common. This method ignores all spaces and punctuation and just sorts based on the next letter of the alphabet. Unfortunately, Excel (and all of Office, I assume) uses a completely different method, which is useless to me. As an example, a term in quotation marks currently gets sorted to the top, above the A terms, but I need it to ignore the opening quote and sort by whatever letter follows it. Also, this may not be part of CMOS but I would like to get terms starting with "The" to ignore that initial "The" and sort by whatever follows it.

Adding a bunch of new terms to a spreadsheet one at a time, so I can manually stick them where they go between the existing rows, would take up an enormous amount of time. So, is there a way I can customize the rules for alphabetization? I've heard of custom sort orders, but it seems like those are meant more for short, finite lists and not for general rules. A web search has turned up no useful ideas. If I can't get things to sort properly, I think I might have to find another program because Excel does not sustainably meet my needs without this function. Please let me know if this is something that can be done (and if so, please break down the steps as much as possible because I am not an Excel expert by any means).

If this CANNOT be done, is there any way I could paste in a list of new terms at the bottom of a spreadsheet and tell Excel to alphabetize ONLY those new terms into the rest, so I could save time and get most of them into place with minimal cleanup afterward?

Thanks in advance for any advice!