VBA Tip: The Replace Function
The Replace function replaces values with others inside a string (it is more or less equivalent to the SUBSTITUTE function in Excel).
Usage:
Replace(text, old_value, new_value)
or
Replace(text, old_value, new_value, start, count, case_sensitive)
Example of usage
To better understand, here's a simple example where the goal is to replace -
with spaces:
Sub example()
myString = "US-34-763-92-EN-1"
MsgBox Replace(myString, "-", " ") 'Returns: US 34 763 92 EN 1
End Sub
Tip: To remove values, enter an empty string ""
as the replacement value:
Sub example()
myString = "US-34-763-92-EN-1"
MsgBox Replace(myString, "-", "") 'Returns: US3476392EN1
End Sub
Example with "start" and "count"
In some cases, it is useful to replace only a part of the found values using the function. In this case, the "count" argument needs to be provided.
In this new example, the goal is to format US-34-763-92-EN-1
to obtain the result US 34.763.92.EN-1
.
To avoid text cutoff, leave "start" at 1 and enter the number of replacements to be made in "count".
Sub example()
myString = "US-34-763-92-EN-1"
'Replace the first 4 hyphens with periods
myString = Replace(myString, "-", ".", 1, 4)
'Replace the first period with a space
myString = Replace(myString, ".", " ", 1, 1)
'Result
MsgBox myString 'Returns: US 34.763.92.EN-1
End Sub
Example with "case_sensitive"
The last optional argument allows you to specify whether or not to consider case sensitivity (by default, the function differentiates between uppercase and lowercase).
To ignore case sensitivity, enter the value 1:
Sub example()
myString = "US-34-763-92-EN-1"
MsgBox Replace(myString, "-en", "") 'Returns: US-34-763-92-EN-1
MsgBox Replace(myString, "-en", "", , , 0) 'Returns: US-34-763-92-EN-1
MsgBox Replace(myString, "-en", "", , , 1) 'Returns: US-34-763-92-1
End Sub