Hello Rowan,

I can't work out how to make this work. I to am using Excel. Can you break
down the actual actions so I can follow them. I have a list of almost 900
that I need to alphabeticalize and want to move the THE, A & AN to the end of
the title - as in: Bible, The

Can you help?

Thanks, Alex

On Monday, November 7, 2005 at 4:19:17 AM UTC, Rowan Drummond wrote:
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!


Try...

=IF(LEFT(A2,4)="The ",MID(D3,5,256)&", The",IF(LEFT(A2,2)="A ",MID(A2,3,256)&",
A",IF(LEFT(A2,3)="An ",MID(A2,4,256)&", An",A2)))

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion