How to Change "The Title" to "Title, The" in Excel

Have you ever tried to sort a list of titles, only to end up moving the "The" or another prefix from your sort? Here's a formula that will do this for you:

Difficulty: Moderate

Instructions

Things You'll Need:

• Excel Spreadsheet
• List of phrases in a column that you want to reorganize

1. Here's the formula that will change a cell containing text "Article Title" to "Title, Article":
   
   =IF(LEFT(Title,LEN(Article))=Article,RIGHT(Title,LEN(Title)-LEN(Article))&", "&Article,Title)
   
   Let's walk through it:

2. Write the len function to calculate the length of the cell. Using cell A1 for this example, we'll convert the song title "The Beat(en) Generation" to "Beat(en) Generation, The"
   
   Formula
   =LEN(A1)
   
   Result for "The Beat(en) Generation"
   23

3. Calculate the length of your string, excluding the 4-character "The " (three letters and a space)
   
   Formula
   =LEN(A1) - 4
   
   Result for "The Beat(en) Generation"
   19

4. Use the right function to select the right-most 19 characters in the string:
   
   Formula
   =RIGHT(A1,LEN(A1)-4)
   
   Result for "The Beat(en) Generation"
   Beat(en) Generation

5. 
   Example using the Formula Below

   Append the ", The" to the end of your string using &" ,The"
   
   Formula
   =RIGHT(A1,LEN(A1)-4)&", The"
   
   Result for "The Beat(en) Generation"
   Beat(en) Generation, The

6. Now we can get really fancy, and first do this only if "The " is found at the beginning of the string. If the article is not found, this formula returns the original title. (In this example, we store "The " in cell $A$2):
   
   Formula
   =IF(LEFT(A1,LEN($A$2))=$A$2,RIGHT(A1,LEN(A1)-LEN($A$2))&", "&$A$2,A1)
   
   Result for "The Beat(en) Generation"
   Beat(en) Generation, The
   
   Result for "This is the Day"
   This is the Day

Tips & Warnings

• "The " is just one example of a leading word. You can do the same with other articles like "A" and "An"
• You can name cells to make your formula easier to read, as in Step 1.
• Remember to use $ to fix your columns and row reference, such as if you reference cell containing "The"
• It's a good idea to Copy/PasteValues to "Fix" the results of your formulas before re-sorting a portion of the spreadsheet.

Resources

• The band, The The