I made a excel spreadsheet listing all the movies that I own. I'm wanting to
put them in alphabetical order. I know how to sort by ascending and
decending. My question is how do I sort so that A, AN, and THE are ignored.

Thanks!

Hi

Use a helper column where you remove A, AN and THE, using the formula below
(one line), then select both columns and sort by the helper column.

=IF(LEFT(A2,3)="An ",RIGHT(A2,LEN(A2)-3),IF(LEFT(A2,4)="The
",RIGHT(A2,LEN(A2)-4),IF(LEFT(A2,2)="A ",RIGHT(A2,LEN(A2)-2),A2)))

Hopes this helps.
....
Per


"Lubslimegreen" skrev i
meddelelsen ...
I made a excel spreadsheet listing all the movies that I own. I'm wanting
to
put them in alphabetical order. I know how to sort by ascending and
decending. My question is how do I sort so that A, AN, and THE are
ignored.

Thanks!

I hope that your movie names are lying like this...

A Column
Movie Names
An Idea
A Bag
The Legend
Soldier

Paste the below formula in B2 cell and drag it to the remaining cells and
the results will be look like this.

=IF(LEFT(TRIM(A2),3)="The",MID(A2,FIND("
",A2)+1,255),IF(LEFT(TRIM(A2),2)="An",MID(A2,FIND( "
",A2)+1,255),IF(LEFT(TRIM(A2),1)="A",MID(A2,FI ND(" ",A2)+1,255),A2)))

B Column
Movie Names
Idea
Bag
Legend
Soldier

Now apply sort for B Column.

But note that, this formula will works when there is space between the
starting words like A, An & The.

Change the formula cell reference of A2 to your desired cell.

If this post helps, Click Yes!

--------------------
(Ms-Exl-Learner)
--------------------



"Lubslimegreen" wrote:

I made a excel spreadsheet listing all the movies that I own. I'm wanting to
put them in alphabetical order. I know how to sort by ascending and
decending. My question is how do I sort so that A, AN, and THE are ignored.

Thanks!

Watch out, this formula is not working exactly as expected, if the title is
'Another Story', your formula will return 'Story', which is not what OP
need.

The formula in my direct reply to OP is returning the correct result.

Regards,
Per

"Ms-Exl-Learner" skrev i meddelelsen
...
I hope that your movie names are lying like this...

A Column
Movie Names
An Idea
A Bag
The Legend
Soldier

Paste the below formula in B2 cell and drag it to the remaining cells and
the results will be look like this.

=IF(LEFT(TRIM(A2),3)="The",MID(A2,FIND("
",A2)+1,255),IF(LEFT(TRIM(A2),2)="An",MID(A2,FIND( "
",A2)+1,255),IF(LEFT(TRIM(A2),1)="A",MID(A2,FI ND(" ",A2)+1,255),A2)))

B Column
Movie Names
Idea
Bag
Legend
Soldier

Now apply sort for B Column.

But note that, this formula will works when there is space between the
starting words like A, An & The.

Change the formula cell reference of A2 to your desired cell.

If this post helps, Click Yes!

--------------------
(Ms-Exl-Learner)
--------------------



"Lubslimegreen" wrote:

I made a excel spreadsheet listing all the movies that I own. I'm wanting
to
put them in alphabetical order. I know how to sort by ascending and
decending. My question is how do I sort so that A, AN, and THE are
ignored.

Thanks!

Try this:

=IF(OR(LEFT(A1,FIND(" ",A1&" ")-1)={"A","An","The"}),MID(A1,FIND(" ",A1&"
")+1,99),A1)


"Lubslimegreen" wrote:

I made a excel spreadsheet listing all the movies that I own. I'm wanting to
put them in alphabetical order. I know how to sort by ascending and
decending. My question is how do I sort so that A, AN, and THE are ignored.

Thanks!