Sorting a text strings but omitting preceding "A" or "The"
 

I need to sort text strings the may begin with "A" or "The". In these cases I
would like for the sort to ignore the "A" or "The" and sort based off the
second item in the string. Can you set preconditions to the sort?

Example:
The Big Blue Boat
A Tree Grows in Brooklyn
Giant
Four Score is Twenty Times Four


Results:
The Big Blue Boat
Four Score is Twenty Times Four
Giant
A Tree Grows in Brooklyn

Sorting a text strings but omitting preceding "A" or "The"
 

On Tue, 15 Dec 2009 13:03:02 -0800, Pablo
wrote:

I need to sort text strings the may begin with "A" or "The". In these cases I
would like for the sort to ignore the "A" or "The" and sort based off the
second item in the string. Can you set preconditions to the sort?

Example:
The Big Blue Boat
A Tree Grows in Brooklyn
Giant
Four Score is Twenty Times Four


Results:
The Big Blue Boat
Four Score is Twenty Times Four
Giant
A Tree Grows in Brooklyn

Set up a "helper column" and sort on that. The helper column will exclude "the
" or "a " if they start the text string.

If your list starts in A1, then:

B1:
=IF(MIN(SEARCH({"the ","a "},A1&"the a "))=1,
MID(A1,FIND(" ",A1)+1,99),A1)

and fill down as far as required.
--ron

Sorting a text strings but omitting preceding "A" or "The"
 

My suggestion is to inster one column and put the formula
=IF(LEFT(A1,3)="the",MID(A1,5,100),IF(LEFT(A1,2)=" A ",MID(A1,3,100),A1))
then sort all.
Click yes if helped
--
Greatly appreciated
Eva


"Pablo" wrote:

I need to sort text strings the may begin with "A" or "The". In these cases I
would like for the sort to ignore the "A" or "The" and sort based off the
second item in the string. Can you set preconditions to the sort?

Example:
The Big Blue Boat
A Tree Grows in Brooklyn
Giant
Four Score is Twenty Times Four


Results:
The Big Blue Boat
Four Score is Twenty Times Four
Giant
A Tree Grows in Brooklyn