Message from discussion Sort and exclude certain words

Date: Mon, 07 Nov 2005 15:19:17 +1100
From: Rowan Drummond <rowanzsaNotT...@hotmail.com>
User-Agent: Mozilla Thunderbird 1.0.6 (Windows/20050716)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Subject: Re: Sort and exclude certain words
References: <jds217.1y3yty_1131336001.6988@excelforum-nospam.com>
In-Reply-To: <jds217.1y3yty_1131336001.6988@excelforum-nospam.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Message-ID: <#M52tJ14FHA.400@TK2MSFTNGP09.phx.gbl>
Newsgroups: microsoft.public.excel.misc
NNTP-Posting-Host: 148.182.35.129
Path: g2news1.google.com!news3.google.com!newshub.sdsu.edu!msrtrans!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
Lines: 1         

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