How do I sort a column alphabetically not consider "the" or "a"?
 

I would like to sort a worksheet alphabetically ascending, but certain row
has "The" or "A" at the beginning, so the rows with "The" at the beginning
are arranged under "T" and the rows with "A" are categorized under "A". How
do I eliminate this problem without deleting "The" or "A"?

On Mon, 12 Sep 2005 16:28:03 -0700, "anteaters00"
wrote:

I would like to sort a worksheet alphabetically ascending, but certain row
has "The" or "A" at the beginning, so the rows with "The" at the beginning
are arranged under "T" and the rows with "A" are categorized under "A". How
do I eliminate this problem without deleting "The" or "A"?

Use a helper column.

In some blank column contiguous with your table (or you can insert a column),
enter this formula:

=IF(OR(LEFT(A2,1)="a",LEFT(A2,3)="the"),
TRIM(MID(A2,FIND(" ",A2),255)),TRIM(A2))

Then sort on this new column.

After you've done the sorting, you can delete the column.

If you need to do this frequently, you can record a macro.


--ron

This doesn't work if the first word starts with an "A" or a "T". (other than
"A<space" and "The<space")

Biff

"Ron Rosenfeld" wrote in message
...
On Mon, 12 Sep 2005 16:28:03 -0700, "anteaters00"
wrote:

I would like to sort a worksheet alphabetically ascending, but certain row
has "The" or "A" at the beginning, so the rows with "The" at the beginning
are arranged under "T" and the rows with "A" are categorized under "A".
How
do I eliminate this problem without deleting "The" or "A"?

Use a helper column.

In some blank column contiguous with your table (or you can insert a
column),
enter this formula:

=IF(OR(LEFT(A2,1)="a",LEFT(A2,3)="the"),
TRIM(MID(A2,FIND(" ",A2),255)),TRIM(A2))

Then sort on this new column.

After you've done the sorting, you can delete the column.

If you need to do this frequently, you can record a macro.


--ron

Correction:

This doesn't work if the first word starts with an "A" or a "T".

Should be:

This doesn't work if the first word starts with an "A" or "The".

Albany New York
Thermal California

If there is always a space after the "A" and the "The", maybe this:

=IF(LEFT(A1,2)="A ",MID(A1,3,255),IF(LEFT(A1,4)="The ",MID(A1,5,255),A1))

Biff

"Biff" wrote in message
...
This doesn't work if the first word starts with an "A" or a "T". (other
than "A<space" and "The<space")

Biff

"Ron Rosenfeld" wrote in message
...
On Mon, 12 Sep 2005 16:28:03 -0700, "anteaters00"
wrote:

I would like to sort a worksheet alphabetically ascending, but certain
row
has "The" or "A" at the beginning, so the rows with "The" at the
beginning
are arranged under "T" and the rows with "A" are categorized under "A".
How
do I eliminate this problem without deleting "The" or "A"?

Use a helper column.

In some blank column contiguous with your table (or you can insert a
column),
enter this formula:

=IF(OR(LEFT(A2,1)="a",LEFT(A2,3)="the"),
TRIM(MID(A2,FIND(" ",A2),255)),TRIM(A2))

Then sort on this new column.

After you've done the sorting, you can delete the column.

If you need to do this frequently, you can record a macro.


--ron

On Tue, 13 Sep 2005 00:15:20 -0400, "Biff" wrote:

This doesn't work if the first word starts with an "A" or a "T". (other than
"A<space" and "The<space")

Good pickup. (Although it would work properly with "T"; you probably meant
"The"

=IF(OR(LEFT(A2,1)="a ",LEFT(A2,3)="the "),
TRIM(MID(A2,FIND(" ",A2),255)),TRIM(A2))

should work.


--ron