Formatting titles in cells/columns to be "title, The"

[MikeyB]

Anyone know how to format cells/columns to always show a title be formatted looking like.... "Title, The"?? For example, the movie title "The Replacements" or music artists "The Beatles" to automatically format to look like "Replacements, The" and "Beatles, The". My intentions are to make lists in alphabetical order by title or artist without having these show up under the letter "T" alphabeitically. I am going to import a list to excel to avoid having to retype thousands of titles(which have "The" in front) and then would like to re-format them as "title, The".

 

[dave3009]

Hi and welcome

Try

=IF(LEFT(A2, 3)="The", MID(A2, 5, 255)&", The", A2)

 

[MikeyB]

That kind of worked. It only worked for that particluar cell and it duplicated the title... so I have "the beatles" and also "beatles, the". Anyway to make it work for the whole column and without duplicating?

 

[dave3009]

Excel Workbook
	A	B
1	The Beatles	Beatles, The
2	The Shining	Shining, The
3	Rambo	Rambo
4	Rocky	Rocky
Sheet2

Excel 2007

Cell Formulas
Range	Formula
B1	=IF(LEFT(A1, 3)="The", MID(A1, 5, 255)&", The", A1)

 

[MikeyB]

Still not quite right,so maybe Im doing something wrong...Im kind of a novice at excel so sorry for the trouble.
So if I open a blank excel sheet, highlight and copy info from say iTunes and paste it into the blank sheet... then what should I do next?