CodeItBetter Programming Another VB Programming Blog

'String Manipulation - How to Replace Multiple Substrings in a String at Once
Option Explicit
 
Public Function ReplaceMultiple(ByVal Expression As String, ByVal sReplace As String, _
    ParamArray sFind()) As String
 
'Replaces multiple substrings in a string with the character or string specified by sReplace
'Overlap Between Characters in sReplace and sFind Will cause this function to behave
'incorrectly unless you are careful about the order of strings in sFind

    Dim lLBound As Long, lUBound As Long, lCount As Long
    Dim sResult As String
 
    lLBound = LBound(sFind)
    lUBound = UBound(sFind)
 
    sResult = Expression
 
    For lCount = lLBound To lUBound
        sResult = Replace(sResult, CStr(sFind(lCount)), sReplace)
    Next
 
    ReplaceMultiple = sResult
End Function