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