How to Remove Characters in String Using VBA

---

You can use the Replace() method in VBA to remove characters from a string.

The following examples show how to use this method in practice with the following list of strings in Excel:

Example 1: Use VBA to Remove All Occurrences of Character in String (Case-Sensitive)

Suppose that we would like to remove “this” from each string.

We can create the following macro to do so:

Sub RemoveChar()
    Dim i As Integer

    For i = 2 To 8
    Range("B" & i) = Replace(Range("A" & i), "this", "")
    Next i
End Sub

When we run this macro, we receive the following output:

Column B displays each of the strings in column A with each occurrence of “this” removed.

Notice that this macro is case-sensitive.

That is, each occurrence of “this” is removed but each occurrence of “This” is left alone.

Example 2: Use VBA to Remove All Occurrences of Character in String (Case-Insensitive)

Suppose that we would like to remove “this” (regardless of case) from each string.

We can create the following macro to do so:

Sub RemoveChar()
    Dim i As Integer

    For i = 2 To 8
    Range("B" & i) = Replace(LCase(Range("A" & i)), "this", "")
    Next i
End Sub

When we run this macro, we receive the following output:

Column B displays each of the strings in column A with each occurrence of “this” removed.

Notice that this replacement is case-insensitive.

That is, each occurrence of “this” (whether it’s capitalized or not) is removed.

We were able to achieved this by using the LCase method to first convert each string in column A to lowercase before searching for “this” in each string.

Example 3: Use VBA to Remove First N Occurrences of Character in String

Suppose that we would like to remove only the first occurrence of “this” (regardless of case) from each string.

We can create the following macro to do so:

Sub RemoveChar()
    Dim i As Integer

    For i = 2 To 8
    Range("B" & i) = Replace(LCase(Range("A" & i)), "this", "", Count:=1)
    Next i
End Sub

When we run this macro, we receive the following output:

Column B displays each of the strings in column A with only the first occurrence of “this” removed.

Note that we used Count:=1 to remove only the first occurrence of a specific string, but you can replace 1 with any value you’d like to instead remove the first n occurrences of a specific string.

Note: You can find the complete documentation for the VBA Replace method .

The following tutorials explain how to perform other common tasks using VBA: