XL: Ignoring the Articles A, An, and The When Sorting Titles

• Microsoft Excel 98 Macintosh Edition
• Microsoft Excel 97 for Windows
• Microsoft Excel for Windows 95, versions 7.0, 7.0a
• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for the Macintosh, versions 5.0, 5.0a

SUMMARY

It is possible to sort titles in Microsoft Excel and ignore articles such as "A," "An," and "The" while sorting. Normally when sorting in Microsoft Excel, the entire contents of the cell is sorted. It is possible to work around this by creating a formula that ignores the articles.

MORE INFORMATION

When they sort lists that contain the titles of movies, records, or books, most people want to have the list sorted so that any of the initial articles, such as "A," "An," or "The" are ignored during the sort.

If a normal sort in Microsoft Excel is performed on the following data

   A Kiss Before Dying
   Kiss Me, Kiss Me, Kiss Me
   The Kissing Place
   A Kid in King Arthur's Court
   The Egg and I
   Kentucky Blue
   An Egg Named Ed

   A Kid in King Arthur's Court
   A Kiss Before Dying
   An Egg Named Ed
   Kentucky Blue
   Kiss Me, Kiss Me, Kiss Me
   The Egg and I
   The Kissing Place

1. Enter the following data in a worksheet:

         A1: Title
         A2: A Kiss Before Dying
         A3: Kiss Me, Kiss Me, Kiss Me
         A4: The Kissing Place
         A5: A Kid in King Arthur's Court
         A6: The Egg and I
         A7: Kentucky Blue
         A8: An Egg Named Ed
   

2. Type the following formula into the worksheet:

         B2: =IF(LEFT(A2,2)="A ",RIGHT(A2,LEN(A2)-2),IF(LEFT(A2,3)=
                "An ",RIGHT(A2,LEN(A2)-3),IF(LEFT(A2,4)="The ",RIGHT(A2,
                LEN(A2)-4),A2)))
   
   

3. With cell B2 selected, grab the fill handle and fill the formula down through cell B8.

4. Click cell A3.

5. On the Data menu, click Sort. Click the Sort By arrow and choose "(Column B)" (without the quotation marks), and click OK.

6. The sorted data will now look like the following:

         A1: Title
         A2: The Egg and I
         A3: An Egg Named Ed
         A4: Kentucky Blue
         A5: A Kid in King Arthur's Court
         A6: A Kiss Before Dying
         A7: Kiss Me, Kiss Me, Kiss Me
         A8: The Kissing Place
   

REFERENCES

Microsoft Excel 7.0

For more information about sorting data in Microsoft Excel, click Answer Wizard on the Help menu and type:

   sorting data

Microsoft Excel 5.0

For more information about sorting data in Microsoft Excel, click the Search button in Help and type:

   sort