Sorting

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Vernon Balbert - 08 Nov 2007 17:00 GMT

In Excel 2007 is there a way to sort a column and have the sort ignore
leading "the" and "a"? For example, I have a list of titles and many of
them start with "The" as in "The Man Who Shot Liberty Valance" or a
leading "a" as in "A View to a Kill". I'd like to sort these and have
it ignore these leading words so that the first one gets sorted into the
M's and the second into the V's.

Signature

The major difference between a thing that might go wrong and a thing
that cannot possibly go wrong is that when a thing that cannot possibly
go wrong goes wrong it usually turns out to be impossible to get at or
repair. - Douglas Adams, Mostly Harmless

Reply to this Message

Peo Sjoblom - 08 Nov 2007 17:19 GMT

No, you would need a help column that will remove these characters

if the list starts in A2 you can use this in B2

=IF(LEFT(A2,4)="The ",SUBSTITUTE(A2,LEFT(A2,4),""),IF(LEFT(A2,2)="A
",SUBSTITUTE(A2,LEFT(A2,2),""),A2))

copy down as long as needed and sort by the help column

the above will take care of A and The

Signature

Regards,

Peo Sjoblom

> In Excel 2007 is there a way to sort a column and have the sort ignore
> leading "the" and "a"? For example, I have a list of titles and many of
> them start with "The" as in "The Man Who Shot Liberty Valance" or a
> leading "a" as in "A View to a Kill". I'd like to sort these and have it
> ignore these leading words so that the first one gets sorted into the M's
> and the second into the V's.

Reply to this Message

Vernon Balbert - 08 Nov 2007 17:49 GMT

On 11/8/2007 9:19 AM, Peo Sjoblom went clickity clack on the keyboard
and produced this interesting bit of text:

> No, you would need a help column that will remove these characters
>
[quoted text clipped - 6 lines]
>
> the above will take care of A and The

Thank you muchly!

Signature

You, you, and you panic. The rest of you, come with me.

Reply to this Message