Sorting

[Guest]

How can I sort a list of movies alphabetically, ignoring words like 'THE' and
'A'

 

[Guest]

Hi,

You could have the name without the A or THE in a separate column and order
on that column.
Eg:
-Names are in column A starting in row 1
-In column B (or whatever), cell B1 enter the formula:
=RIGHT(A1,LEN(A1)-(2*(LEFT(UPPER(A1),2)="A "))-(4*(LEFT(UPPER(A1),4)="THE
")))
--> returns the name without "THE " or "A ". Note: carefull when typing
it -- there is a space after THE and after A.
-Copy/paste B1 down along the data
-Order that new column

 

[Guest]

ok, i'm new with excel...could you be really specific on what I need to
do....thanks!! or can I email you my sheet?

 

[Guest]

ok, Ronnie, let's go step by step.

Assumption: your movie titles are in cells A2, A3, A4, ...

1- in B2 (or, if column B is already being used by some data, in row2 of the
next available column), enter the formula:
=RIGHT(A1,LEN(A1)-(2*(LEFT(UPPER(A1),2)="A "))-(4*(LEFT(UPPER(A1),4)="THE
")))
2- Copy B2: select B2, menu Edit > Copy
3- Paste Down along the data: select B2:Bx (x corresponding to the last row
of data in column A); goto menu Edit > Paste
Now, in column B, you should see all titles without any leading A and THE
4- Sort the data: select A2:Bx, goto menu Data > Sort, the Sort dialog
pops-up, then sort by Column B ascending or descending.

Let me know if you have any difficulty with any of the above steps.

 

[Guest]

its not even working.....it says there is an error in the formula, or when I
do paste it nothing happens at all............

 

[Guest]

Could you post here
1. in which cells are your movie title located
1. the formula you have entered in the first cell (from the one i have
posted above)

 

[Guest]

My movies titles start at cell A2, A1 being the title "Movie Titles"
I pasted the formula:

=RIGHT(A1,LEN(A1)-(2*(LEFT(UPPER(A1),2)="A "))-(4*(LEFT(UPPER(A1),4)="THE
")))