Author: Oscar Cronquist Article last updated on February 24, 2022

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.

1. How to use the User Defined Function

Substitute multiple text strings UDF1

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 the cells below.

Back to top

2. User Defined Syntax

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

Back to top

3. 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.

Back to top

4. 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

Back to top

5. 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. Press with left mouse button on Insert on the top menu.
  4. Press with left mouse button on 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

Back to top

6. How to save a macro-enabled workbook

Note, save your workbook with file extension *.xlsm (macro-enabled workbook) to keep the code attached to the workbook.

Back to top

Get the Excel file


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