Sort and exclude certain words
 

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!


--
jds217
------------------------------------------------------------------------
jds217's Profile: http://www.excelforum.com/member.php...o&userid=28608
View this thread: http://www.excelforum.com/showthread...hreadid=482709

Sort and exclude certain words
 

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!

Sort and exclude certain words
 

On Sun, 6 Nov 2005 21:57:40 -0600, 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!

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

Sort and exclude certain words
 

On Sunday, November 6, 2005 at 7:57:40 PM UTC-8, 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!


--
jds217
------------------------------------------------------------------------
jds217's Profile: http://www.excelforum.com/member.php...o&userid=28608
View this thread: http://www.excelforum.com/showthread...hreadid=482709

Rowan's formula worked like a charm! Thanks. :)

Sort and exclude certain words
 

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!

Sort and exclude certain words
 

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

Sort and exclude certain words
 

Hi,

Am Sun, 12 Apr 2020 05:28:39 -0700 (PDT) schrieb
:

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

try:
=MID(A1&", "&A1,FIND(" ",A1&", "&A1)+1,LEN(A1)+1)

if it doesn't work provide some examples


Regards
Claus B.
--
Windows10
Office 2016

Sort and exclude certain words
 

Hi again,

Am Sun, 12 Apr 2020 19:12:27 +0200 schrieb Claus Busch:

=MID(A1&", "&A1,FIND(" ",A1&", "&A1)+1,LEN(A1)+1)

to change only the titles with THE, A and An at the start, try:
=IF(OR(LEFT(A1,FIND(" ",A1)-1)={"The";"A";"An"}),MID(A1&", "&A1,FIND(" ",A1&", "&A1)+1,LEN(A1)+1),A1)


Regards
Claus B.
--
Windows10
Office 2016