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