VBA Tip: Replace
The Replace function replaces some of the values with others within a string (this is more or less the equivalent of the Excel SUBSTITUTE function).
Use:
Replace(text, search_value, replace_value)
Example
To illustrate, here's a simple example where we are replacing -
with spaces:
Sub example()
my_string = "US-34-439-92-EN-1"
MsgBox Replace(my_string, "-", " ") 'Returns: US 34 439 92 EN 1
End Sub
To remove values, enter ""
as a replacement value:
Sub example()
my_string = "US-34-439-92-EN-1"
MsgBox Replace(my_string, "-", "") 'Returns: US3443992EN1
End Sub