Actually you don't want to pick off a trailing A on a word, so make that

=SUBSTITUTE(SUBSTITUTE(" " & A20 & " ", " The "," ")," A "," ")

note the space inserted before and after the cell to be tested
and the space before and after the words that are to be removed
and the single space for the replacement. Accept the spaces
remaining when finished because trimming would add another
nesting level.

When you determine all of the words you want to remove and
depending on their location and removal of punctuation you might
be looking at a user defined function since you can only go to
seven nesting levels in a worksheet formula.

But the original question may have only be removal of a
leading The and a Leading A.

=SUBSTITUTE(SUBSTITUTE("^" & A20 & "^", "^The "," "),"^A ","^")
again only used for sorting so it doesn't matter what it looks like.

Might be more suitable for the use of Regular Expressions.
---
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

"Bob Phillips" wrote in message ...
Create a helper column and add this formula to it

=SUBSTITUTE(SUBSTITUTE(A20,"The ",""),"A ","")

Then sort both columns by the helper column

--
HTH

Bob Phillips

"Ronnie" wrote in message
...
ok...I have complied a list of my DVD collection and what I am trying to
do
is alphabatize the list. What problem I have when doing it is when I
click
sort button it always groups words like "A" and "The". I want it to
ignore
those words and focus on the word after "A" or "The"