Sorting

[Ronnie]

I was making a list of movies and I'm wanting to sort them by name, but how
can I get excel to ignore words like "the" and "a" and recognize the first
word after that

[bigwheel]

i've done something similar and my solution was to use the following:-

Sub SortThis()
Do Until IsEmpty(ActiveCell)
strTitle = ActiveCell.Value
numSize = Len(strTitle)
If Left(strTitle, 3) = "The" Then
ActiveCell.Offset(0, 3) = Right(strTitle, (numSize - 4)) & ",The"
ElseIf Left(strTitle, 2) = "A " Then
ActiveCell.Offset(0, 3) = Right(strTitle, (numSize - 2)) & ", A"
Else
ActiveCell.Offset(0, 3) = strTitle
End If
ActiveCell.Offset(1, 0).Select

Loop
End Sub

So, with the first title selected, the macro copies the title to a new
column and tags "The" or "A" to the end of the title. Then you can sort the
resulting range as you wish (add it into the macro perhaps)

"Ronnie" wrote:

> I was making a list of movies and I'm wanting to sort them by name, but how
> can I get excel to ignore words like "the" and "a" and recognize the first
> word after that

[Jim Cone]

Ronnie,

My brand new Excel add-in "Special Sort" (beta2) will do that.
It ignores leading "A", "An", "The" and sorts the list for you.
It also provides six additional "special" methods to sort with.

Available -free- upon direct request. Remove XXX from my email address.

Jim Cone
San Francisco, USA
[email protected]XX


"Ronnie" <Ronnie @discussions.microsoft.com> wrote in message
news:[email protected]...
I was making a list of movies and I'm wanting to sort them by name, but how
can I get excel to ignore words like "the" and "a" and recognize the first
word after that

[David McRitchie]

Hi Ronnie,
Another way just using worksheet formulas:
SUBSTITUTE is case sensitive

Do you include "The" in the sort if it is the first word?

Create a helper column.
=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER(A16)," and "," ")," the "," ")," a "," ")," on "," ")," in ","
"))

The Wind in the Willows the wind willows
The Cat and the Fiddle the cat fiddle
Thousand and One Space Odessey thousand one space odessey
The Prince and the Pauper the prince pauper

If you want the as the first word to disappear
instead of TRIM(A16) use
=" " & A16
and then you could add one more substitution

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Ronnie" <Ronnie @discussions.microsoft.com> wrote in message news:[email protected]...
> I was making a list of movies and I'm wanting to sort them by name, but how
> can I get excel to ignore words like "the" and "a" and recognize the first
> word after that

[Bill Ridgeway]

It is all very well relying on doing things automatically with, say, macros.
The problem with this approach is that unless the macro is well written and
positively tested to cover all conditions you can get into the "it must be
OK if it was done by the computer" mentality and have a database with some
'funnies'. Either testing to make sure the macro is comprehensive or
checking after running you may have to check every record.

I would suggest a helper column with the first 5 significant characters of
the title. Sorting on this column should do the job.

Regards.

Bill Ridgeway
Computer Solutions

"Ronnie" <Ronnie @discussions.microsoft.com> wrote in message
news:[email protected]...
>I was making a list of movies and I'm wanting to sort them by name, but how
> can I get excel to ignore words like "the" and "a" and recognize the first
> word after that