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

[robbyvegas]

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

[zbor]

Maybe this:

=IF(LEFT(B2, 14)="Translation of",TRIM(SUBSTITUTE(B2,"Translation of","")&" with translation"),B2)

[StevenM]

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

Sub TranslationOf2WithTranslation()
    Dim nRow As Long, nLastRow As Long, s As String
    Dim nLen As Long
    nLen = Len("Translation of ")
    nLastRow = Cells(Rows.Count, "B").End(xlUp).Row
    For nRow = 2 To nLastRow
        s = Cells(nRow, "B")
        If Len(s) >= nLen Then
            If Left$(s, nLen) = "Translation of " Then
                s = Right$(s, Len(s) - nLen)
                Cells(nRow, "B") = s & " with translation"
                Cells(nRow, "B").Interior.Color = vbRed
            End If
        End If
    Next nRow
End Sub

[robbyvegas]

Awesome. Worked like a dream! Thanks!

[johnjohns]

You can use wildcards in excel also.
or this macro

Sub ChangeIt()
  Dim OneCell As Range
  For Each OneCell In Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))
     If InStr(1, OneCell, "Translation of ") >= 1 Then
        OneCell.Value = Application.WorksheetFunction.Substitute(OneCell.Value, "Translation of ", "") & " with translation"
        OneCell.Interior.ColorIndex = 38
     End If
  Next OneCell
End Sub

[robbyvegas]

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!!!