SUBSTITUTE with VLOOKUP for text replacement

[Pedroman]

Hi All

I am sure this is possible, so hopefully you can help

I am trying to translate UK English To US English for a program that uses Excel for content translations

1 sheet has all my content in cells (sentences)
Another I have a column "A" UK words and "B" US Words

I need a formula please that sees a UK word in a cell (could be more than one) and replaces it with the US equivalent

E.g.

The fertiliser was used on the centre field = The fertilizer was used on the center field

It seems i want an amalgamation of SUBSTITUTE with VLOOKUP. Basically it is Find/Replace but for allot of words (500+)

Any ideas please

 

[Takae]

This case very hard to express with formula because a sentence have many words. It has to make each word independent and compare.
Please try below macro.
Paste on sheet1 module and assuming you have a correspondence table on sheet2. When you change columnA(UK) of sheet1, it will run.

Sheet2 should be
columnA column B
fertiliser fertilizer
centre center

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long, LR As Long, a, buf As String, rtn As String
    If Intersect(Target, Range("A:A")) Is Nothing Then
        Exit Sub
    Else
        a = split(Target.Value, " ")
        
        For i = 0 To UBound(a)
            On Error Resume Next
            rtn = Application.WorksheetFunction.VLookup(a(i), Sheets("sheet2").Range("A:B"), 2, 0)
            If rtn = "" Then rtn = a(i)
            buf = buf & rtn & " "
            rtn = ""
            On Error GoTo 0
        Next
        buf = Left(buf, Len(buf) - 1)
    End If
    cells(Target.row, 2).Value = buf
End Sub