Santa Clara University

summer-web

Software Tips & Tricks

Back to Blog

How to sort titles alphabetically but ignore 'a', 'an', and 'the' in Excel

excel icon

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.

  1. Enter the 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. Add a heading in B1, like "Field title"
  3. 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)))
  4. Hit ENTER to close the formula.
  5. With cell B2 selected, grab the fill handle and fill the formula down through cell B8.
  6. Click cell A3.
  7. 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.

 

More information | Posted by Technology Training

Tags: Excel

Tags
Printer-friendly format
 
Established 1851