Find and Replace Characters in a String in Excel VBA

Replace() function Syntax

Replace (Expression, Find, Replace, Start, Count, Compare)

• Expression – The expression is a string in which you want to find a character(s) to replace. This can be an array or a value in a cell from your worksheet.

• Find – The character or set of characters you want to find in an Expression (the string) to replace.

• Replace – The characters or set of characters you want to replace with, in an Expression.

• Start – The starting position you want the search to begin (in the Expression). This parameter is Optional. The default starting position is 1. Therefore, using this parameter you can start the search from another position, say 5.

• Count – Define Count (a numeric value) to find the number of occurrences to replace. This parameter is Optional. If you do not define a Count in your Replace function, VBA will find and replace all the characters in string that you wish to replace.

• Compare – You have three options to choose.

1) vbBinaryCompare – Performs a Binary comparison. This is the default option.
2) vbDataBaseCompare – Performs a comparison based on information in your database.
3) vbTextCompare – Performs a textual comparison

When you use all the parameters in the Replace function, it will look like this.

Replace(cell.Value, "P", "N", 3, 1, vbBinaryCompare)

OR

Replace(strVal, "a", "ai", 4, 1, vbBinaryCompare)

1) Example Using Replace() Function

In my first example, I’ll show you the basic use of the Replace function.

Dim str As String
str = "pan, nal, sal"
str = Replace(str, "a", "ai")

Debug.Print str

I have a string variable with values separated by commas. Using the Replace() function, I’ll find the character "a" in the string and replace it with "ai" The result will be,

pain, nail, sail

Note: I am printing the result (using Debug.Print) in the immediate window of the VBA editor.

2) Replace String Starting at the nth Character

In the above example, I have used only the first three parameters to replace a string. Since, I wanted to find and replace the specified character in the entire string.

However, I wish to ignore few characters in the string and start the search from a specified location only. For example, now I want to search and replace the character a after the first comma (ignoring the first string characters). I’ll now use the fourth parameter (Start) in the function.

There are two way I can assign a value to the Start option in the function.

i) I can directly assign a numeric value (6 in this case), immediately after the 3 (replace) option.

str = "pan, nal, sal"
str = Replace(str, "a", "ai", 6)

ii) I can explicitly use the keyword Start with := and assign the value (6). If there are no other parameter in-between, still you can assign a value. See the 3rd example below (using Count).

str = "pan, nal, sal"
str = Replace(str, "a", "ai", Start:=6)

3) Replace Only the Third Instance Using Count Option

I have three different values in a string, separated by two commas. I now wish to replace only the third instance of the character a, with a new character(s). That is (using the above string in the example), I want to ignore a in pan and nal and replace only sal. Its really simple.

str = "pan, nal, sal"
str = Replace(str, "a", "ai", Count:=3)

Now, see how I am using the Count keyword explicitly to define the number of occurrence. I am using the keyword, since the fourth parameter (see the syntax) is for option Start. Here, I am telling VBA to consider the value according to its keyword.

4) Replace Values in a Range of Strings in your Worksheet

Now, let us see how we can replace characters in a range of values in our Excel worksheet. I have eight rows of data in column B. I also have a button (an ActiveX control) on sheet1.

I want to replace the character N with P in all the rows. Here’s my code, inside the button’s click event.

Option Explicit

Private Sub CommandButton1_Click()
    Dim myDataRng As Range
    Dim cell As Range

    ' SET THE RANGE (SECOND COLUMN).
    Set myDataRng = Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row)

    For Each cell In myDataRng
        If InStr(1, cell.Value, "N") > 0 Then
            cell.Value = Replace(cell.Value, "N", "P")
            cell.Font.Color = vbRed
        Else
            cell.Font.Color = vbBlack
        End If
    Next cell
End Sub

The Result

The values in the above example are hardcoded. You can even make is a little more dynamic (and interesting), by asking for an input from the user. There will be a slight change in the above code. I am adding an InputBox() method in the Macro.

Set myDataRng = Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row)

Dim str As String
str = Application.InputBox("Enter a Character")

If Trim(str) <> "" Then
    For Each cell In myDataRng
        If InStr(1, cell.Value, UCase(str)) > 0 Then
            cell.Value = Replace(cell.Value, UCase(str), "P")
            cell.Font.Color = vbRed
        Else
            cell.Font.Color = vbBlack
        End If
    Next cell
End If

Now you can replace any given character with the character P on your worksheet (with in column B). You can define a broader range.

5) Using Count Option with Replace Function in a Range

You can use the count option with the Replace function to manipulate strings in a worksheet range. For example, I have rows of data in the second (B) column.

DP0 DN0 DP0
IP0 PAN NAP

Both the string has the character P multiple times. However, I wish to change the first occurrence (ONLY) of the character P in the strings. Here’s my code.

cell.Value = Replace(cell.Value, UCase(str), "N", Count:=1)

The Result

DN0 DN0 DP0
IN0 PAN NAP

The Replace function in VBA is useful when you want to manipulate a set of characters in a string with a new set of characters. It may look similar to the Find and Replace option in Excel. However, it has many other benefits and used specifically at run time using a Macro. You can set a specific location (using Start option) or a specific number of occurrences (using Count option) to replace.

← PreviousNext →