Sort and exclude certain words - microsoft.public.excel.misc

Message from discussion Sort and exclude certain words

Ron Rosenfeld

View profile

More options Nov 7 2005, 11:39 pm

Newsgroups: microsoft.public.excel.misc

From: Ron Rosenfeld <ronrosenf...@nospam.org>

Date: Mon, 07 Nov 2005 07:39:05 -0500

Local: Mon, Nov 7 2005 11:39 pm

Subject: Re: Sort and exclude certain words

  Reply to author
  |
  Forward
  | Print
  | View thread
  | Show original
  | Report this message
  | Find messages by this author
  

On Sun, 6 Nov 2005 21:57:40 -0600, jds217

<jds217.1y3yty_1131336001.6...@excelforum-nospam.com> wrote:

>Hi ...

>I work in a public library and frequently export lists of books from
>our online catalog to Excel, so that I can manipulate the data in
>various ways.

>My problem in sorting in Excel is this: Our library catalog excludes
>words like "The" "A" or "An" at the beginning of a title when sorting.
>Excel, however, does not.

>Is there any way I can sort on a field in Excel and have it ignore
>certain words at the beginning of a string?

>Thanks!

I wonder about your card catalog. For example, is the book "A Tree Grows in
Brooklyn" cataloged under the "A's" or the "T's"? (or both).

In any event, there are various methods, all of which involve constructing a
separate list that excludes the initial word, and sorting on this new list.

If there are just a few words to be excluded, and not much change in the list,
then a simple, nested IF function as recommended by Rowan will work just fine.

If there are a larger number of words, then a more general solution could be
had by using the following formula to construct your "word-stripped" list.

=IF(COUNTIF(ListOfWords,LEFT(A1,FIND(" ",A1)-1)),
TRIM(MID(A1,FIND(" ",A1)+1,255)),A1)

ListOfWords is a range where you have listed all of the first words which you
want excluded. You may either NAME the range, or substitute the reference in
the formula (e.g. Z1:Z10)

VBA solutions are also available.

--ron

Reply to author Forward