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

TheMSsForum.com: The Microsoft Software Forum

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?



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/
Top

Re: Sorting ignoring alpahnumeric lists ignoring "the" and "an". by Tom

Tom
Sun Oct 26 12:23:17 CST 2003

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

--
Regards,
Tom Ogilvy


pbrute <pbrute.vx3oz@excelforum-nospam.com> wrote in message
news:pbrute.vx3oz@excelforum-nospam.com...
> 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?
>
>
>
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~ View and post usenet messages directly from http://www.ExcelForum.com/
>


Top

Re: Sorting ignoring alpahnumeric lists ignoring "the" and "an". by David

David
Sun Oct 26 17:37:13 CST 2003

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..
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Tom Ogilvy" <twogilvy@msn.com> wrote in message news:vpo49nn47eb3bd@corp.supernews.com...
> 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
>
> pbrute <pbrute.vx3oz@excelforum-nospam.com> wrote in message
> news:pbrute.vx3oz@excelforum-nospam.com...
> > 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?


Top