Excel.Tips.Net Welcome toExcel.Tips.Net

Helpful Links

Tips.Net Home
ExcelTips Home
Ask an Excel Question
Make a Comment

Tips.Net Store

ExcelTips FAQ
ExcelTips Premium

Learn Access Now
Free Printable Forms

Beauty Tips
Car Tips
Cleaning Tips
College Tips
Cooking Tips
Excel2007 Tips
ExcelTips
Family Tips
Gardening Tips
Health Tips
Home Tips
Legal Tips
Money Tips
Organizing Tips
Pest Tips
Pet Tips
Wedding Tips
Word2007 Tips
WordTips

Advertise on the
ExcelTips Site

Newest Tips

Recording a Macro

Adding a Little Animation to Your Life

Converting a Range of URLs to Hyperlinks

Making the Formula Bar Persistent

Engineering Calculations

Digital Signatures for Macros

Fixing the Decimal Point

 

Ignoring Selected Words when Sorting

Summary: If you use Excel to maintain a list of text strings (such as movie, book, or product titles), you may want the program to ignore certain words when sorting that list. This can't be done automatically, but there are ways to get your list in the order you want. (This tip works with Microsoft Excel 97, Excel 2000, Excel 2002, Excel 2003, and Excel 2007.)

Arn has a need to exclude certain words when sorting a column. For instance, he is trying to exclude 'The' when sorting a list of movie titles, so that "Alpha, Charlie, The Bravo" would sort as "Alpha, The Bravo, Charlie."

There is no built-in way to do this. The best solution is to set up an intermediate column for your data. This column can contain the modified movie titles, and you can sort by the contents of the column. For instance, if column A contains your original movie titles, you could fill column B with formulas, such as this:

=IF(LEFT(A1,4)="The ",MID(A1,5,LEN(A1)-4),A1)

This formula will strip the word "The" (with its trailing space) from the start of the line. If you want to add the word "The" at the end of the string, then you could modify the formula in the following manner:

=IF(LEFT(A1,4)="The ",MID(A1,5,50),A1) & ", The"

If you wanted to delete all instances of the word "the" without regard to where it appeared in the title, you could use the following instead:

=SUBSTITUTE(A1,"the ","")

Sorting, again, would be done by the results shown in column B. This will give the list in the desired order.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3892) applies to Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Save Time and Money! Many people need to keep track of employee time, but don't know where to start when it comes to creating a spreadsheet. Here's a way to save time, effort, and money with ready-to-use timesheet templates.
 
Check out Timesheet Templates today!