Sorting ignoring alpahnumeric lists ignoring "the" and "an".

  • Thread starter Thread starter pbrute
  • Start date Start date
P

pbrute

I am trying to sort a large alphanumeric list of titles, but wish to do
so igniring definitive articles such as "The" and "an".

Is there anyone that can tell me how to do this?
 
create a dummy column using

=Substitute(Substitute(substitute(A1,"the ",""),"an ",""),"And ","")
then drag fill down the column. Sort on the column with the formula
 
Would modify Tom's suggestion slightly so that you are actually removing words
and not possibly character strings at the end of a word. Also Substitute
is case sensitive so you want to work with either uppercase or lowercase.

You can only nest to a level of 7 so you can add only two more substitutions
to the following: ( I added one additional substitute to Tom's)

=TRIM(substitute(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(" " & LOWER(A1) & " "," the ","")," an ","")," and ","")," a ",""))

Somewhat along these lines, is a macro on one of my pages proper.htm
which will allow the first word to be capitalized but the not capitalize
certain words within, which is a lot better than simply using the
PROPER worksheet function..
 
Thank you for taking the time to respond, but your suggestions are not
working in my case.

I am trying to compile a list of titles that have The, An, A on th the
title, but wish to sort ignoring these indefinitive articles.

Using your formula, the title "A Room with a View" ends looking like
"room withview", and therefore does not sort correctly.

What would work is a formula that would ignore the first three letters
during sorting if they equal "The", "A", or "An".

Thanking you in advance....
 
=TRIM(IF((ISNUMBER(FIND(" ",A1))),IF(OR(LEFT(A1,FIND("
",A1)-1)={"A","An","The"}),SUBSTITUTE(A1,LEFT(A1,FIND("
",A1)-1),"",1),A1),A1))
 
Note that there should be a space between the " " in the find part,
I imagine it is easy to stripa that away if the lines are wrapping

--

Regards,

Peo Sjoblom

Peo Sjoblom said:
=TRIM(IF((ISNUMBER(FIND(" ",A1))),IF(OR(LEFT(A1,FIND("
",A1)-1)={"A","An","The"}),SUBSTITUTE(A1,LEFT(A1,FIND("
",A1)-1),"",1),A1),A1))
Click to expand...
 
Peo,
Thank you so much for taking the time to write up the formula, it now
works perfectly. I would not have been able to figure this out on my
own.

Thank you again!
 
Back
Top