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

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?
 
G

Gord Dibben

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
 
G

Guest

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?
 
D

David Biddulph

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

Open File menu sort alphabetical. 6
Set add function ignore #N/A 1
How to sort names in alphabetical ascending order? 7
alphabetical sort while ignoring preceeding numbers/symbols 1
Sorting of No.Of Worksheets in a Workbook 1
writing a sort macro 2
Cannot sort data 6
sort by zip code 2

Top