Need to batch-change: "The AnyMovieTitle" - - - to - - - "AnyMovieTitle, The" in a column

Hello there!

I have an Excel 2003 database, containing movie titles in one really long column.
What I need to accomplish, may be quite tricky, but here goes...

I would like to REMOVE the FIRST occurrence of "The " (and the space after it) from the beginning of each movie title
(ONLY the ones that START with: "The " of course), and then add: ", The" to the END of the movie title. (comma,space,"The")

>>> Movie title, BEFORE running the macro:
"The Monster From Behind The Dumpster At The KFC"
(notice that there are 3 occurrences of "The" - - - - the 2nd,3rd, etc... occurrences, are to be ignored, of course).

>>> Movie title, AFTER running the macro:
"Monster From Behind The Dumpster At The KFC, The"

I hope that somebody out there knows how to code a macro, that can do this...
I am certainly not looking forward to doing over 6,000 titles, manually... yikes!

Thank-You for your time, in reading my humble request for help.
Best Regards;
Lrrr.

I would do this with a helper column...

	A	B
1	The aaa bbb ccc	aaa bbb ccc, The
2	aaa bbb ccc	aaa bbb ccc

B1=IF(LEFT(A1,4)="the ",MID(A1,5,99)&", The",A1)
copied down.

You could then copy/paste values from the helper over your original date, then delete the helper, if needed

Thanks FDibbins, but unfortunately that would be just as much work as doing it manually.
It's ANY form of "editing", that I am really trying to avoid.

There is no editing, you copy down and its all done. If needed, you could then copy/paste values

Did you even try it?

Go ahead and post the file and I'll get it done for you.

Click go advanced at the bottom of the box you type your replies into.
Then click on the paperclip icon and post your spreadsheet.

View Pic

skywriter: Go ahead and post the file and I'll get it done for you.

skywriter: Click go advanced at the bottom of the box you type your replies into.
Then click on the paperclip icon and post your spreadsheet.

Attached, is one of the TV-Show spreadsheets - the Movie ones will also work the same way...
I have been fooling around with some "helper columns" at the right, but
I still can't get my desired result, without cutting and pasting - which
is what I would like to avoid.
Thank-You sooooo much!
Lrrr

Hi Ford;
It's all the copying and pasting, that I am trying to avoid.
I estimate thousands of this change to make...

You are not using the formula I suggested. You have...
=IF(LEFT(E4,4)="The ",MID(E4,5,99)&", The",E4:E4=I4)

1st, change the format from TEXT to GENERAL
2nd, use this, copied down...
=IF(LEFT(E4,4)="The ",MID(E4,5,99)&", The",E4)

Once you have that...
double-click the square inthe botton-right corner of the cell - that should copy the formula all the way down

Now the problem is, that I can't cut and paste the result that is achieved in column I, because it is a "calc" column.
So, no copying I to E.

On another note;
I moused-over your reputation bar, and "beyond repute" is incorrect - it should be "beyond reproach", or "above reproach".

Lrrr

Add this macro to your sheet.
Select all the movie titles or whatever it is you want to change and run the macro.
The easiest way to select is select the top title press Control + Shift and then the down arrow and it will select them all the way to the bottom, then run the macro.

Good Luck!!!

Please Login or Register  to view this content.

Hi Lrrr

You'll not need Helper Columns if the we can change the Data directly in Column E. Is that OK?

That is EXACTLY what I am looking for!
AWESOME!
How are you able to DO that, when the SOURCE TEXT is in column E?
Lrrr

Now the problem is, that I can't cut and paste the result that is achieved in column I, because it is a "calc" column.
So, no copying I to E.

Not sure what you mean by that?
For the copy/paste...
1. Highlight I4-down
2. press CTRL C (Copy)
3. Click on E4
4. Seclect Paste special - Values

I moused-over your reputation bar, and "beyond repute" is incorrect - it should be "beyond reproach", or "above reproach".

I will take that as a good comment

Paste special - Values...... AAAhhhhhhhhhh - didn't know that.
Thanks, buddy!
Lrrr.

Hi Lrrr
Assuming this

if the we can change the Data directly in Column E. Is that OK?

Try this

Please Login or Register  to view this content.

Im happy we got this resolved for you Thanks for the feedback

Originally Posted by jaslake

Hi Lrrr
Assuming this

Try this

Please Login or Register  to view this content.

Hi there, John!
Thank-You soooo much for your code!
The only little problem that occurred, was, whenever a title starts with a word like "There" (as in "There Goes The Neighborhood"),
it changes it to "re Goes The Neighborhood, The".
If the space after the word "The" was considered (as in "The ", I'm sure it could work perfectly!

Best Regards;
Lrrr

Hi Lrrr

Change the Line of Code

Please Login or Register  to view this content.

To

Please Login or Register  to view this content.

(add a space in "The ")