Author: Oscar Cronquist Article last updated on January 20, 2020

Substitute multiple text strings UDF1

The SUBSTITUTE and REPLACE functions can only handle one string,  the following User-Defined Function (UDF) allows you to substitute multiple text strings with new text strings.

You may have as many strings as you like, there is really no limit. The image above shows the UDF in cell C3 using strings from E3:E4 and F3:F4.

Formula in cell C3:

=SubstituteMultiple(A2,$D$2:$D$3,$E$2:$E$3)

The UDF will not appear and work yet until you have copied the code below to a module in your workbook. There are instructions below.

Got it working? Now copy cell C3 and paste to cells below, the first argument contains relative cell references meaning they will change automatically when you copy and paste cell C3 to cells below.

The second and third argument are absolute cell references, they contain dollar signs meaning they are locked to cell range $D$2:$D$3 and $E$2:$E$3. These cell references will not change when you copy cell C3 and paste to cells below.

User Defined Syntax

SubstituteMultiple(text As String, old_text As Range, new_text As Range)

Arguments

text Required. A cell reference to a cell containing the text you want to manipulate.
old_text Required. A cell reference to one or many cells containing strings you want to replace.
new_text Required. A cell reference to one or many cells containing strings you want instead.

VBA code

'Name function and dimension argument variables and declare their data types
Function SubstituteMultiple(text As String, old_text As Range, new_text As Range)

'Dimension variable and declare data type
Dim i As Single

'Iterate through cells in argument old_text
For i = 1 To old_text.Cells.Count

    'Replace strings in value based on variable i
    Result = Replace(LCase(text), LCase(old_text.Cells(i)), LCase(new_text.Cells(i)))

    'Save manipulated value to variable text
    text = Result
Next i

'Return value stored in variable Result to worksheet
SubstituteMultiple = Result
End Function

Where to put the code?

Copy code to vb editor

  1. Copy above VBA code.
  2. Go to the Visual Basic Editor (Shortcut keys Alt + F11).
  3. Click Insert on the top menu.
  4. Click Module to create a module in your workbook. A module named module1 appears in the Project Explorer.
  5. Paste code to module.
  6. Exit VB Editor
Note, save your workbook with file extension *.xlsm (macro-enabled workbook) to keep the code attached to the workbook.