VBA: How do I remove all the non-printable characters from a string?

---

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

This method uses the following basic syntax:

Replace(expression, find, replace, start, count)

where:

• expression: The string you want to replace characters in
• find: The string to find
• replace: The string to use as replacement
• start (optional): The starting position in the string to be searched
• count (optional): The number of replacements to make

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

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

Suppose that we would like to replace “this” with “THAT” in each string.

We can create the following macro to do so:

Sub ReplaceChar()
    Dim i As Integer

    For i = 2 To 8
    Range("B" & i) = Replace(Range("A" & i), "this", "THAT")
    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” replaced by “THAT”.

Notice that this replacement is case-sensitive.

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

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

We can create the following macro to do so:

Sub ReplaceChar()
    Dim i As Integer

    For i = 2 To 8
    Range("B" & i) = Replace(LCase(Range("A" & i)), "this", "THAT")
    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” replaced by “THAT”.

Notice that this replacement is case-insensitive.

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

We were able to perform this case-insensitive replacement 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 Replace First N Occurrences of Character in String

Suppose that we would like to replace only the first occurrence of “this” (regardless of case) with “THAT” in each string.

We can create the following macro to do so:

Sub ReplaceChar()
    Dim i As Integer

    For i = 2 To 8
    Range("B" & i) = Replace(LCase(Range("A" & i)), "this", "THAT", 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” replaced by “THAT”.

Note that we used Count:=1 to replace only the first occurrence of a specific string, but you can replace 1 with any value you’d like to instead replace 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: