Sorting - Ignoring a, an, the

[Guest]

I am using Excel 2000 and I have a list of music artists running down the A column. Some of their names are not properly identified unless "The" appears first (i.e. The Beatles, The Clash, etc.). I want to sort the list but have certain articles like a, an, the ignored in the sort (i.e. Bauhaus, The Beatles, Bon Jovi).

Could anyone help me with this one? Thanks.

 

[Nick Hodge]

Shawn

This will almost certainly require a new column stripping off the
exceptions. If there is a definitive this of these it should be fairly
simple. If not... almost impossible, because the function/user-defined
function or macro used to parse the data for the new column would need to
know this.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

I am using Excel 2000 and I have a list of music artists running down the

A column. Some of their names are not properly identified unless "The"
appears first (i.e. The Beatles, The Clash, etc.). I want to sort the list
but have certain articles like a, an, the ignored in the sort (i.e. Bauhaus,
The Beatles, Bon Jovi).

 

[Domenic]

Hi Shawn,

The following formula will deal with any name that begins with either an
"a", "an", or "the":

=IF(ISNUMBER(FIND(" ",A1)),IF(OR(LEFT(A1,FIND("
",A1)-1)={"a","an","the"}),RIGHT(A1,LEN(A1)-FIND(" ",A1)),A1),A1)

You can include any other article you wish within the OR statement.

Hope this helps!

 

[Debra Dalgleish]

If you aren't using any Excel features, such as calculation, you could
copy the list to a table in Word.
Format the words such as "A", "An", "The", as hidden text (Format>Font)
Sort the list (Table>Sort)
When you want to print, display the hidden text (Tools>Options, View tab).

 

[Domenic]

Hi Shawn,

The following formula will deal with any name that begins with either an
"a", "an", or "the":

=IF(ISNUMBER(FIND(" ",A1)),IF(OR(LEFT(A1,FIND("
",A1)-1)={"a","an","the"}),RIGHT(A1,LEN(A1)-FIND(" ",A1)),A1),A1)

You can include any other article you wish within the OR statement.

Hope this helps!

Forgot to mention to enter the formula in B1 and copy down as far as you
need to.

Then select Column B
Copy > Paste Special > Values
Click Ok

Select your data in Columns A and B
Data > Sort
Sort by Column B
Click Ok

Then you can delete Column B.

 

[Harlan Grove]

I am using Excel 2000 and I have a list of music artists running
down the A column. Some of their names are not properly identified
unless "The" appears first (i.e. The Beatles, The Clash, etc.). I
want to sort the list but have certain articles like a, an, the
ignored in the sort (i.e. Bauhaus, The Beatles, Bon Jovi).

....

In the first blank column to the right of your table, and assuming the first
artist's name is in cell A2, enter the following formula in the row 2 cell
in that first blank column to the right

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(lower(A2),"a",""),
"an",""),"the",""))

and fill this cell down as far as needed. Sort the table on this added
column.

 

[Harlan Grove]

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(lower(A2),"a",""),
"an",""),"the",""))

Bad idea! Make that

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(lower(" "&A2)," a ",""),
" an ","")," the ",""))