Sort and exclude certain words

[jds217]

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!

 

[Rowan Drummond]

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

 

[Ron Rosenfeld]

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