Substitute multiple text strings [UDF]
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:
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 above VBA code.
- Go to the Visual Basic Editor (Shortcut keys Alt + F11).
- Click Insert on the top menu.
- Click Module to create a module in your workbook. A module named module1 appears in the Project Explorer.
- Paste code to module.
- Exit VB Editor
Weekly Blog EMAIL
Welcome! I am Oscar and here to help you out.Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
Related articles
How to count word frequency in a cell range [UDF]
This user defined function creates a unique distinct list of words and how many times they occur in the selected […]
Extract unique distinct values from a filtered Excel defined Table [UDF and Formula]
Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection […]
List files in a folder and subfolders [UDF]
This article demonstrates a user defined function that lists files in a ggiven folder and subfolders. A user defined function is […]
Split words in a cell range into a cell each [UDF]
This post describes how to split words in a cell range into a cell each using a custom function. I […]
Split values equally into groups
Question: How do I divide values equally into groups (3 lists or less)? This post shows you two different approaches, […]
8 Responses to “Substitute multiple text strings [UDF]”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Do you have a version that is case sensitive?
Ari,
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(text, old_text.Cells(i), new_text.Cells(i)) text = Result Next i SubstituteMultiple = Result End FunctionGreat solution, thanks!
this is best solution from whole google search results , Thanks very much.
but there is little difference within Substitute and SubstituteMultiple:
Substitude Multiple Changes Texts UperCase symbols To LowCase, can someone help how to fix this?
Zurab Lomidze,
Thank you. Try this udf, it is case sensitive.
Function SubstituteMultipleCS(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(text, old_text.Cells(i), new_text.Cells(i)) text = Result Next i SubstituteMultipleCS = Result End FunctionYou saved my day, Thanks
omgness, thank you so much
Great post, Oscar; I use this function all the time. A problem I need help with is that the replacement keeps looping through my replaced text. For example, I have a "Remarks" column with a cell containing "A,B". Each character represents an index item from a multi-column table elsewhere.
A = ALMOND BERRY
B = BLUE COCONUT
Here is the formula I'm running:
=SubstituteMultiple("A,B",{"A";"B"},{"ALMOND BERRY";"BLUE COCONUT"})
I want to produce the following result:
"ALMOND BERRY,BLUE COCONUT"
However, the following results from the SubstituteMultiple UDF:
"ALMOND BLUE COCONUTERRY,BLUE COCONUT"
The problem is that the index character is an alphabetical character that appears in the second of the replacement phrase, thus causing a replacement loop on the second term in the "new_text".
What are your thoughts? Thanks for your effort.