Here are a few VBA functions that I cooked up to replace all instances of a selected portion of string within string. I am sure that this isn't anything new but it's my take on it and it may save some people some time from having to figure it all out.
Aside from calling this in VBA Code, you can also use the VBA function in a Microsoft Access query or control on a form. The sample file demonstrates how it can be used in a spreadsheet.
ReplaceInStr() [Same function as Replace(), but I wrote this as an exercise]
Example: Replace all instances of "X" with "1"
i.e. =ReplaceInStr("X+1","X",1) = "1+1"
MultiReplaceInStr()
Example: Replace all instances of "A" with "1", "B" with "2", and "C" with "3". Must create two strings; one with the 'to-be-replaced' strings as a single string with commas separating the individual strings, and similarly one with the 'replacement strings'. The first replacement is done for the entire string first, then the second once the first is done, and so on.
i.e. =MultiReplaceInStr("A+B+C","A,B,C","1,2,3") = "1+2+3"
MultiReplaceInStrArray()
Example: Replace all instances of "A" with "1", "B" with "2", and "C" with "3". First provide a 'to-be-replaced' string, then the 'replacement string, and so on. The first replacement is done for the entire string first, then the second once the first is done, and so on.
i.e. =MultiReplaceInStrArray("A+B+C+ABC","A","now I know my ABC","A",1,"B",2,"C",3) = "now I know my 123+2+3+now I know my 12323"
CountInStr() - Used by MultiReplaceInStr()
e.g. CountInStr("q,w,e,r,t,y",",") = 5
VBA Code
Option Compare Database 'Only needed for Microsoft Access VBA
Option Explicit
Public Function ReplaceInStr(ByVal strString As String, _
ByVal strReplacee As String, _
ByVal varReplacer As Variant) As Variant
'**************************************************************
'ReplaceInStr()
'Written by Azli Hassan
'
'PURPOSE: To replace all instances of a given string
' within a string.
'
'ARGUMENTS:
'1) strString (String)
' - String te be changed
'2) strReplacee (String)
' - String to be replaced
'3) varReplacer (Variant)
' - Variant to replace strReplacee in strString
'
'RETURNS: (Variant) - Either a string or an error value
' e.g. ReplaceInStr("X+1","X",1) = "1+1"
'**************************************************************
On Error GoTo ErrMsg:
Dim lngPos As Long, lngRight As Long, _
strLeft As String, strRight As String, _
strReplaced As String
strReplaced = strString
lngPos = InStr(1, strReplaced, strReplacee)
While lngPos <> 0
strLeft = Left(strReplaced, lngPos - 1)
lngRight = Len(strReplaced) - lngPos - Len(strReplacee) + 1
strRight = Right(strReplaced, lngRight)
strReplaced = strLeft & varReplacer & strRight
lngPos = InStr(lngPos + Len(varReplacer), strReplaced, strReplacee)
Wend
ReplaceInStr = CStr(strReplaced)
ExitHere:
On Error Resume Next
Debug.Print "ReplaceInStr = " & ReplaceInStr
Exit Function
ErrMsg:
Debug.Print Err.Number & " - " & Err.Description
ReplaceInStr = CVErr(Err.Number)
Resume ExitHere:
End Function
Public Function MultiReplaceInStr(ByVal strString As String, _
ByVal strMultiReplacee As String, _
ByVal strMultiReplacer As String, _
Optional strDelimiter As String = ",") _
As Variant
'**************************************************************
'MultiReplaceInStr()
'Written by Azli Hassan
'
'PURPOSE: To replace all instances of a
' multiple given strings within a string.
'
'ARGUMENTS:
'1) strString (String)
' - String te be changed
'2) strMultiReplace (Array of type Variant)
' - Delimited string of string parts to be replaced
'3) strMultiReplacer (String)
' - Delimited string of replacement strings
'3) strDelimited (String)
' - Delimiter string (Default = ",")
'
'Example:
' MultiReplaceInStr("A+B+C","A,B,C","1,2,3") = "1+2+3"
'
'RETURNS: Either a string or an error value
'**************************************************************
On Error GoTo ErrMsg:
Dim lngCountReplacee As Long, lngCountReplacer As Long
'Check that the number of string parts to be replaced
'is the same number of replacement strings
lngCountReplacee = CountInStr(strMultiReplacee, strDelimiter)
lngCountReplacer = CountInStr(strMultiReplacer, strDelimiter)
If lngCountReplacee <> lngCountReplacer Then
Err.Raise 13
End If
Dim iReplacee As Long, iReplacer As Long
iReplacee = 1
iReplacer = 1
Dim i As Long
Dim lngPosReplacee As String, lngPosReplacer As String
Dim strReplacee As String, strReplacer As String
Dim lngMultiReplacee As String, lngMultiReplacer As String
MultiReplaceInStr = strString
For i = 1 To lngCountReplacee + 1
'Find Position of delimiter
lngPosReplacee = InStr(iReplacee, strMultiReplacee, strDelimiter)
lngPosReplacer = InStr(iReplacer, strMultiReplacer, strDelimiter)
'Get replacee string
If lngPosReplacee = 0 Then
strReplacee = strMultiReplacee
Else
strReplacee = Mid(strMultiReplacee, iReplacee, lngPosReplacee - iReplacee)
lngMultiReplacee = Len(strMultiReplacee) - lngPosReplacee
strMultiReplacee = Right(strMultiReplacee, lngMultiReplacee)
End If
'Get replacer string
If lngPosReplacee = 0 Then
strReplacer = strMultiReplacer
Else
strReplacer = Mid(strMultiReplacer, iReplacer, lngPosReplacer - iReplacer)
lngMultiReplacer = Len(strMultiReplacer) - lngPosReplacer
strMultiReplacer = Right(strMultiReplacer, lngMultiReplacer)
End If
'Replace
MultiReplaceInStr = ReplaceInStr(MultiReplaceInStr, strReplacee, strReplacer)
Next i
ExitHere:
On Error Resume Next
Debug.Print "MultiReplaceInStr = " & MultiReplaceInStr
Exit Function
ErrMsg:
Debug.Print Err.Number & " - " & Err.Description
MultiReplaceInStr = CVErr(Err.Number)
Resume ExitHere:
End Function
Public Function CountInStr(ByVal strString As String, _
strFind As String) As Long
'**************************************************************
'CountInStr()
'Written by Azli Hassan
'
'PURPOSE: To count all instances of a
' given string within a string.
'
'ARGUMENTS:
'1) strString (String)
' - String te be changed
'2) strFind (String)
' - String to find and count within strString
'
'Example:
' CountInStr("q,w,e,r,t,y",",") = 5
'
'RETURNS: Either a string or an error value
'**************************************************************
On Error GoTo ErrMsg:
Dim lngPos As Long, lngRight As Long, _
strLeft As String, strRight As String
CountInStr = 0
lngPos = InStr(1, strString, strFind)
While lngPos <> 0
CountInStr = CountInStr + 1
strLeft = Left(strString, lngPos - 1)
lngRight = Len(strString) - lngPos - Len(strFind) + 1
strRight = Right(strString, lngRight)
strString = strLeft & "" & strRight
lngPos = InStr(1, strString, strFind)
Wend
ExitHere:
On Error Resume Next
Debug.Print "CountInStr = " & CountInStr
Exit Function
ErrMsg:
Debug.Print Err.Number & " - " & Err.Description
CountInStr = CVErr(Err.Number)
Resume ExitHere:
End Function
Public Function MultiReplaceInStrArray(ByVal strString As String, _
ParamArray aReplace() As Variant) _
As Variant
'**************************************************************
'MultiReplaceInStrArray()
'Written by Azli Hassan
'
'PURPOSE: To replace all instances of a
' multiple given strings within a string.
'
'ARGUMENTS:
'1) strString (String)
' - String te be changed
'2) aReplace (Array of type Variant)
' - Array of string to be replaced and replacement string.
' Order of array is
' i) String to be replaced
' ii) Replacement string
'
'Example:
' MultiReplaceInStrArray("A+B+C","A",1,"B",2,"C",3) = "1+2+3"
'
'RETURNS: Either a string or an error value
'**************************************************************
On Error GoTo ErrMsg:
If (UBound(aReplace) - LBound(aReplace) + 1) Mod 2 <> 0 Then
Err.Raise 13
End If
Dim i As Long
MultiReplaceInStrArray = strString
For i = LBound(aReplace) To UBound(aReplace) Step 2
MultiReplaceInStrArray = MultiReplaceInStr(MultiReplaceInStrArray, CStr(aReplace(i)), aReplace(i + 1))
Next i
ExitHere:
On Error Resume Next
Debug.Print "MultiReplaceInStrArray = " & MultiReplaceInStrArray
Exit Function
ErrMsg:
Debug.Print Err.Number & " - " & Err.Description
MultiReplaceInStrArray = CVErr(Err.Number)
Resume ExitHere:
End Function
Sample File
Sample file (Microsoft Excel) can be downloaded from here.