Sorting Spreadsheet Help

L

Lubslimegreen

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!
 
P

Per Jessen

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
 
M

Ms-Exl-Learner

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,FIND(" ",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!
 
P

Per Jessen

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
 
T

Teethless mama

Try this:

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top