Prev: Count occurences of same zip codes
Next: Concatenate Cells
From: Pablo on 15 Dec 2009 16:03
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

From: Ron Rosenfeld on 15 Dec 2009 16:30
On Tue, 15 Dec 2009 13:03:02 -0800, Pablo <Pablo(a)discussions.microsoft.com>
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
From: Eva on 15 Dec 2009 17:10
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
>
 | 
Pages: 1
Prev: Count occurences of same zip codes
Next: Concatenate Cells