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?
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.
> 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?
>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.
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)
|
