Sort and exclude certain words

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!

One way:

If your data is in column A starting in row 2 then in any unused column
in row 2 enter the formula:
=IF(LEFT(A2,4)="The ",MID(A2,5,256),
IF(LEFT(A2,2)="A ",MID(A2,3,256),
IF(LEFT(A2,3)="An ",MID(A2,4,256),A2)))

Copy this down as far as needed and then select all your data and sort
on this new column.

Hope this helps
Rowan

jds217 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!
>
>

On Sun, 6 Nov 2005 21:57:40 -0600, jds217
<[email protected]> 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