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

Message from discussion Sort and exclude certain words

Rowan Drummond

View profile

More options Nov 7 2005, 3:19 pm

Newsgroups: microsoft.public.excel.misc

From: Rowan Drummond <rowanzsaNotT...@hotmail.com>

Date: Mon, 07 Nov 2005 15:19:17 +1100

Local: Mon, Nov 7 2005 3:19 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
  

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!

Reply to author Forward