 "Pin It")
Excel MVP Oz Du Soleil contacted me a few days ago with an interesting Excel challenge: “how could Excel alphabetize titles, but ignore A, An or The if a title begins with one of those?”
So, we start with these titles…
And we should see the titles sorted like this…
Essential Question
Regardless of how we solve this Excel puzzle, this is the initial question that we have to answer:
We need to answer this for each Title that we have. And don’t forget that we have to display the full Title but sort them excluding A, An, and The (if found to the left of the first space).
My Thought Process
From what I remember it went something like this: …should I search for each exclude word individually or all at once using an array? I ended up using an array formula as the key step! I know….many people don’t use arrays but I find them so helpful when working in Excel. My solution allows exclude words to be easily added or removed using an Excel table.
What Was Oz Thinking?
Oz’s solution is less complicated than mine and easier to explain or hand off to a non technical person. Each exclude word is listed as a column header with formulas below to check each title. A final formula is used to sort in the correct order. If you suffer from Arrayphobia then use Oz’s solution!
Watch Oz’s YouTube Video
Wait! Can This Be Solved Without Formulas???
Hmmm…..a good challenge. I eventually found a way to solve this challenge without using any formulas! I combined several built-in Excel features: Text To Columns, Find/Replace, Go To Special, Sort.
Watch My YouTube Video!
Download my Excel file
Download here or via my OneDrive (file 00149)
Update: Solved With VBA!
Chris Newman from www.thespreadsheetguru.com solved it using VBA!
Check out his code here ! Thanks Chris! I’m going to play around with the code on the weekend!
About Me
My name is Kevin Lehrbass. I live in Markham, Ontario, Canada. I’ve been studying, supporting, building, troubleshooting, teaching and dreaming in Excel since 2001. I’m a Data Analyst.
Hundreds of millions of people use Excel everyday. Thousands of hours can be saved and costly errors can be avoided or corrected if you study this powerful software. Check out my videos and my blog posts.
Away from Excel I enjoy playing with my dog Cali (she doesn’t like Excel), learning Spanish and playing Chess.