Microsoft KB Archive/151447

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

Article ID: 151447

Article Last Modified on 8/17/2005

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition
 * Microsoft Excel 95 Standard Edition
 * Microsoft Excel 5.0 Standard Edition
 * Microsoft Excel 98 for Macintosh

-



This article was previously published under Q151447



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.



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:

 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

 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)))

 With cell B2 selected, grab the fill handle and fill the formula down through cell B8. Click cell A3. On the Data menu, click Sort. In the Sort By list, click Column B, and click OK. 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

</ol>

<div class="references_section">

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.

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

Additional query words: XL98 XL97 XL7 XL5 XL

Keywords: kbhowto KB151447

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.