Script or formula to move text from beginning to end of a string

Hi,

In Word I'd use wildcards to do this. I'm not sure how to do it in Excel.

I need to search a column for the text "Translation of" if that text is found, I need to delete the text and put "with translation" at the end of the string. An added bonus would be if I could have it automatically highlight the cell in Red if it was changed. Here's a sample with before and after tables: Sample.xlsx

Thanks!
Rob

Maybe this:

Formula:

Please Login or Register  to view this content.

Copy code into a standard module (if you don't know how, ask).
Run: TranslationOf2WithTranslation

Please Login or Register  to view this content.

Awesome. Worked like a dream! Thanks!

Originally Posted by johnjohns

You can use wildcards in excel also.

Search and Replace doesn't have a "use wildcards" box that will allow you to do things like this
Find what: Translation of (*)
Replace with: \1 with translation

Am I missing something?
Thanks for all of the help!!!