Sorting
 

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

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
--
Regards,
Sébastien


"Ronnie" wrote:

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

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?

"sebastienm" wrote:

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
--
Regards,
Sébastien


"Ronnie" wrote:

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

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.
--
Regards,
Sébastien


"Ronnie" wrote:

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?

"sebastienm" wrote:

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
--
Regards,
Sébastien


"Ronnie" wrote:

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

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

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)
--
Regards,
Sébastien


"Ronnie" wrote:

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

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