- Code: Select all
Private Function ReplaceAll(ByVal Op As String, _
ByVal Ex As String, _
Optional ByVal Rp As String) _
As String
'' 0070 V 1.0
' ==================================================
' Parameters:
' Op = Original string to be operated on
' Ex = Exclude by replacing or removing
' Rp = String replacing Ex
' [if not supplied, Ex is removed]
' ==================================================
Dim Lft As String ' Left String (= Result)
Dim n As Integer ' = Start of Ex
' ==================================================
Do
n = InStr(Op, Ex)
If n Then
Lft = Lft & Left(Op, n - 1)
Op = Mid(Op, n + Len(Ex))
' don't add a space before a space or before the end:
Lft = Lft & IIf(Rp = " " And (Left(Op, 1) = " " Or _
Len(Op) = 0), "", Rp)
End If
Loop While n > 0
ReplaceAll = Lft & Op
End Function
- You pass a string in which to do the replacing and receive in return a string in which replacements have been made.
- As a second parameter you pass the string to be replaced. That could be a single character or a string of characters of any length.
- The third parameter is the replacement string. This argument is optional. If you don't supply it the string to be replaced will be replaced with nothing, meaning it will be removed.
- Code: Select all
MyString = ReplaceAll(MyString, ",")
All occurrences of the string to be replaced will be found, not only the first one. If the removal of a string leads to two blank spaces following each other the second or final blank will also be removed.
This procedure is particularly useful if called from a loop that examines each cell in a range of cells such as this one:
- Code: Select all
Dim R As Long
For R = 1 To LastRow
With Cells(R, "A")
.Value = ReplaceAll(.Value, "my", "your")
End With
Next R
The function LastRow can be copied from this forum:
http://www.excelkey.com/forum/viewtopic.php?f=5&t=393
When deploying the above procedure remember to change its scope to 'Public' if you call it from another worksheet.