Replacing

There is one built-in function and one built-in statement you can use for replacing strings.


REPLACE(expression, find, replace [,start] [,count] [,compare])

You can use the REPLACE function to return a text string with a number of characters replaced.
This built-in function is case sensitive by default.

Replace("C:\Temp\", "C:\", "E:\") = "E:\Temp\" 
Replace("C:\Temp\", "C:\", "E:\", 1) = "E:\Temp\"
Replace("c:\Temp\", "C:\", "E:\", 1) = "c:\Temp\"

If you would prefer the function to not be case sensitive, you can change the default "compare" argument.
vbCompareMethod

Replace("c:\Temp\", "C:\", "E:\", 1, , vbCompareMethod.vbTextCompare) = "E:\Temp\" 

MID(stringvar, start [,length]) = string

You can use the MID - Statement to replace a specified number of characters with characters from another string.
Not to be confused with the MID - Function which returns a specified number of characters.

Dim sText As String 
sText = "C:\Temp\"
Mid(sText, 1, 1) = "E"
Debug.Print sText '= "E:\Temp\"

Dim sText As String
sText = "C:\Temp\"
Mid(sText, 4, 4) = "Personal"
Debug.Print sText '= "C:\Personal\"

Replacing from the middle

You can use the INSTR function to return the position of a substring within a larger string, starting at the beginning.
You can use the LEFT function to return a number of characters from the left of a string.
You can use the RIGHT function to return a number of characters from the right of a string.

Dim sText As String 
Dim sNewText As String
Dim istartchar As Integer
Dim iendchar As Integer
sText = "This is very long text"
istartchar = InStr(sText, " is ") + Len(" is ") - 1
iendchar = InStr(sText, " text")

sNewText = Left(sText, istartchar) & _
           "quite short" & _
           Right(sText, Len(sText) - iendchar + 1)
Debug.Print sNewText '= "This is quite short text"

Replacing the first occurrence

You can use the REPLACE function to return a text string with a number of characters replaced.

Dim sText As String 
sText = "The First Occurrence"
Debug.Print Replace(sText, "e", "", 1, 1)
'= "Th First Occurrence"

Dim sText As String
sText = "AA BB CC BB AA"
Debug.Print Replace(sText, "BB ", "", 1, 1)
'= "AA CC BB AA"

Replacing the last occurrence

You can use the STRREVERSE function to return the text string with the characters reversed.

Dim sText As String 
sText = "The First Occurrence"
Debug.Print StrReverse(Replace(StrReverse(sText), StrReverse("e"), StrReverse(""), 1, 1))
'= "The First Occurrenc"

Dim sText As String
sText = "AA BB CC BB AA"
Debug.Print StrReverse(Replace(StrReverse(sText), StrReverse("BB "), StrReverse(""), 1, 1))
'= "AA BB CC AA"

Replacing double quotes

You can use the REPLACE function to return a text string with a number of characters replaced.

Dim sText As String 
sText = "Replace ""double"" quotes"
Debug.Print Replace(sText, Chr(34), "'")
'= "Replace 'double' quotes"

Dim sText As String
sText = "Replace ""double"" quotes"
Debug.Print Replace(sText, """", "'")
'= "Replace 'double' quotes"

Replacing line break

You can use the REPLACE function to return a text string with a number of characters replaced.

Dim sText As String 
sText = "first line" & vbCrLf & "second line" & vbCrLf & vbCrLf
Debug.Print Replace(Replace(sText, Chr(10), ""), Chr(13), "")
''= "first linesecond line"

Dim sText As String
sText = "first line" & vbCrLf & "second line" & vbCrLf & vbCrLf
Debug.Print Replace(sText, vbNewLine, "")
''= "first linesecond line"

© 2022 Better Solutions Limited. All Rights Reserved. © 2022 Better Solutions Limited TopPrevNext