How to change value of characters?

[tremaine]

Hi,

I use excel for keeping a database of all the movies I have seen.

My problem is in regard to the value of characters. I sort my movies by default alphabetically and am wondering if there is any way to exclude the word 'The' from the sort. Traditionally when movies are sorted alphabetically, that word does not count, but obviously my problem is that excel is indeed including it.

There is also another problem regarding the value of characters. When I rate movies less than a '6' I would like to write it as "<6". The problem is that excel actually reads the "<" symbol as being of greater value than numbers and will put any movie rated "<6" at the top of the row when I sort largest to smallest. Is there any way to change the value of the "<" symbol so that my movies rated "<6" will be sorted below all my other rated movies?

Any help is much appreciated.

Thanks

[powdow]

Create another column for a sorting title and enter this formula:

=IF(LEFT(A2,3)="The",MID(A2,5,LEN(A2)-4)&", "&LEFT(A2,3),A2)

This will format the movie titles like "The Great Gatsby" to "Great Gatsby, The".

[Pepe Le Mokko]

Create a custom format like [<6]"<6" that you apply to your range.
When you enter a value lower than 6 you will see "<6", the underlying value being what you entered and so you can sort as usual

[MrShorty]

I sort my movies by default alphabetically and am wondering if there is any way to exclude the word 'The' from the sort. Traditionally when movies are sorted alphabetically, that word does not count, but obviously my problem is that excel is indeed including it.

Any restriction on the solution? I know a lot of data bases like this that I have seen control this problem at the data entry step. They tend to move the "the" to end of the text string (i.e. Computer Who Wore Tennis Shoes, The -- Man from Snowy River, The). In many ways, I think the easiest solution to this problem is to train yourself to put the "the" at the end of the text string when you enter the movie title.

One solution for the <6 part of the problem is similar -- Rather than <6, use 7 as your lowest possible score. For this part of the problem, you could also create a custom list http://office.microsoft.com/en-us/ex...333.aspx?CTT=1

[powdow]

Same thing for your rating, create a sort rating column and enter this formular:

=IF(C2="<6",VALUE(RIGHT(C2,1)),C2)

You can then hide either or both columns if you wish.

[tremaine]

Yeah, sorry I should have mentioned that I have considered doing it like "Great Gatsby, The" but much prefer it with "The" at the start.

[tremaine]

Same thing for your rating, create a sort rating column and enter this formular:

=IF(C2="<6",VALUE(RIGHT(C2,1)),C2)

You can then hide either or both columns if you wish.

I'm a newbie at excel. I pasted the formula you gave me into the first cell of an empty column and tried sorting my ratings largest to smallest but the ratings of "<6" are still appearing at the top.

[powdow]

Attached is example of adding movie sort and rating sort columns with the formulas. You may need to ensure your rating sort column is formatted as a number.

[tremaine]

Attached is example of adding movie sort and rating sort columns with the formulas. You may need to ensure your rating sort column is formatted as a number.

Oh right I see, thanks. So you can hide it when not using it, but have to unhide/show it when you want to sort? Why can't I use that same formula for just the one column? Again, I'm a newbie...

[tremaine]

Oh I think I get it...is it because you need a reference as to what value you want it to be?

[Pepe Le Mokko]

Have you tried my ( simple) suggestion? It's not as sophisticated as other answers but it does what you need with a minimum of fuss

[tremaine]

Have you tried my ( simple) suggestion? It's not as sophisticated as other answers but it does what you need with a minimum of fuss

I'm a real newbie so I don't even know how to do it. You suggested a custom format and that I apply it to the range.

[ARGK]

Hi
Here is another idea:
1. In column A you the first word you dont want Sort to use in this column, for example "The".
2. In column B you put the remainder of the title.
3. In column C you enter this formula: =A1&" "&B1, this will join up your Movie Titles to their original formats.
4. You can then use column B as your sort column and Column C will display the normal format of your movie titles.
Hope this helps.
Good luck.
Tony