Ignore "a(n)", "the" in A-Z sort

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm sure it's easy, but I can't find this anywhere. I need to sort, ignoring
A, AN, THE, so everythging is in the correct alphabetical order. Any help?
 
A Bob Phillips suggestion from Nov. 13th

Create a helper column and strip leading The and A and An,

=Substitute(Substitute(Substitute(A1,"The ",""),"A ",""),"An","")

Sort on the stripped column.


Gord Dibben MS Excel MVP
 
Create a helper column and strip leading The and A and An,

But is it possible for them to remain, but still be sortable, just ignoring?
 
They will remain in the original column. Select all the relevant columns
(including the original and the helper column), sort by the helper column,
and that will sort the original ignoring the bits you've stripped out.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

sorting numerical data 2
Columns not sorting properly. 2
Open File menu sort alphabetical. 6
Sorting by "High", "Medium" and "Low" 7
How to ignore parens in a sort? 3
Data Sorting Problem 2
Sorting of No.Of Worksheets in a Workbook 1
How to sort names in alphabetical ascending order? 7

Back
Top