Back to Blog
How to sort titles alphabetically but ignore 'a', 'an', and 'the' in Excel
The best tips for Microsoft Excel often come from Microsoft itself. The following steps from the Microsoft Support Knowledge Base will allow you to sort titles in Excel that begin with the articles 'a', 'an', and 'the'. This uses an example data set, but you can adapt the formula for your own purposes.
- Enter the data in a worksheet:
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
- Add a heading in B1, like "Field title"
- Type the following formula into the worksheet into cell 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)))
Hit ENTER to close the formula.
- 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.
Need to brush up on your Excel skills? Sign up for our introductory Excel class: Up and Running with Excel. Or download the handout and class files here.