Technology Training - Welcome to Technology Training

SCU » Welcome to Technology Training

Welcome to Technology Training

Technology Training is dedicated to delivering software training and support to Santa Clara students, faculty, and staff. We offer offer several learning formats including classes, open lab hours, consultations, and web help that are tailored to help users learn software used in teaching, learning, and scholarship.

Back to Blog

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

view full size »

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:
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
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.

Posted by Technology Training - Monday, Oct. 26, 2009 | More Info

For Those With Special Needs

The Training & Instruction rooms have select ADA compliant computer stations that are wheelchair accessible. Trainers are committed to working with our special needs community to allow the best possible use of technology. Please contact us for further assistance.