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

  • Thread starter Thread starter Pablo
  • Start date Start date
P

Pablo

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
 
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
Click to expand...

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
 
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
 
Back
Top