Author: Oscar Cronquist Article last updated on January 09, 2018

The user defined function demonstrated below replaces multiple old text strings with new text strings in a cell. The custom function is case insensitive.

Substitute multiple text values

Formula in cell B2:


VBA code

Function SubstituteMultiple(text As String, old_text As Range, new_text As Range)
Dim i As Single
For i = 1 To old_text.Cells.Count
    Result = Replace(LCase(text), LCase(old_text.Cells(i)), LCase(new_text.Cells(i)))
    text = Result
Next i
SubstituteMultiple = Result
End Function
  1. Go to VB Editor (Alt+F11)
  2. Click Insert
  3. Click Module
  4. Paste code to module
    Copy code to vb editor
  5. Exit VB Editor

Download excel *.xlsm file

Substitute existing text with multiple text strings in a cell range.xlsm