Substitute multiple text strings [UDF]
The user defined function demonstrated below replaces multiple old text strings with new text strings in a cell. The custom function is case insensitive.
Formula in cell B2:
=SubstituteMultiple(A2,$D$2:$D$3,$E$2:$E$3)
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
- Go to VB Editor (Alt+F11)
- Click Insert
- Click Module
-
Paste code to module
- Exit VB Editor
Download excel *.xlsm file
Substitute existing text with multiple text strings in a cell range.xlsm
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 [โฆ]
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 [โฆ]
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, [โฆ]
5 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.
English
Spanish
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 Function