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

J

jmj713

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
 
J

jmj713

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