Article ID: 151447 - Last Review: August 17, 2005 - Revision: 1.3

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

Retired KB ArticleRetired KB Content Disclaimer
View products that this article applies to.
System TipThis article applies to a different operating system than the one you are using. Article content that may not be relevant to you is disabled.
This article was previously published under Q151447

On This Page

Expand all | Collapse all

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 are sorted. It is possible to work around this by creating a formula that ignores the articles.
Back to the top

MORE INFORMATION

When sorting 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
it would look as follows after the sort:
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
To sort a list that contains titles that begin with one of the initial articles, follow these steps:

  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. In the Sort By list, click Column B, 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
Back to the top

REFERENCES

Microsoft Excel 97

For more information about sorting data, click Contents And Index on the Help menu, click the Index tab in Excel Help, type the following text
sorting
and then double-click the selected text to go to the "sort a list" topic. If you are unable to find the information you need, ask the Office Assistant.
Back to the top

Microsoft Excel 7.0

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

Microsoft Excel 5.0

For more information about sorting data in Microsoft Excel, click the Search button in Help and type:
sort
Back to the top
APPLIES TO
  • Microsoft Excel 97 Standard Edition
  • Microsoft Excel 95 Standard Edition
  • Microsoft Excel 5.0 Standard Edition
  • Microsoft Excel 98 for Macintosh
Back to the top
Keywords: 
kbhowto KB151447
Back to the top
Retired KB ArticleRetired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
Back to the top