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

[Guest]

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"?

 

[Ron Rosenfeld]

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

 

[Biff]

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

Biff

 

[Biff]

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

 

[Ron Rosenfeld]

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