Excel VBA: How to Replace Text in a String - 5 Methods

This is the sample dataset.

Method 1 – Using Excel VBA to Replace a Text Starting in the n-th Position of a Random String

Step 1:

Go to the Developer Tab >> Code >> Visual Basic.

In the Visual Basic Editor:

Go to Insert>> Module

A Module will be created.

Step 2:

Enter the following code

Sub substitution_of_text_1()
Dim full_txt_str, updated_str As String
full_txt_str = "Hundred Cars Fifty Cars Ten Cars"
updated_str = Replace(full_txt_str, "Cars", "Bicycles", 1)
MsgBox updated_str
End Sub

full_txt_str and updated_str were declared as String and full_txt_str was assigned to a random text string- “Hundred Cars Fifty Cars Ten Cars”. The VBA REPLACE function is used to replace Cars with Bicycles and 1 is used here to start the replacement from position 1 of this string. The new text string is assigned to updated_str and the result is displayed in a message box (MsgBox).

Press F5.
A message box will be displayed with the new text: Bicycles.

To replace Cars in another text string, use the following code.

Sub substitution_of_text_1()
Dim full_txt_str, updated_str As String
full_txt_str = "Hundred Cars Fifty Cars Ten Cars"
updated_str = Replace(full_txt_str, "Cars", "Bicycles", 14)
MsgBox updated_str
End Sub

14 is the starting position, to replace the string after Hundred Cars.

Run the code.
The following message box will be displayed.

To replace the last last portion of this string only, use the following code.

Sub substitution_of_text_1()
Dim full_txt_str, updated_str As String
full_txt_str = "Hundred Cars Fifty Cars Ten Cars"
updated_str = Replace(full_txt_str, "Cars", "Bicycles", 25)
MsgBox updated_str
End Sub

25 is the starting position to return the string after Fifty Cars and replace Cars with Bicycles.

The following message box will be displayed.

Method 2 – Substituting a Text in the n-th Occurrence of a Random String with VBA

Steps:

Follow Step 1 in Method 1.
Enter the following code.

Sub substitution_of_text_2()
Dim full_txt_str, updated_str As String
full_txt_str = "Hundred Cars Fifty Cars Ten Cars"
updated_str = Replace(full_txt_str, "Cars", "Bicycles", 1, 1)
MsgBox updated_str
End Sub

full_txt_str and updated_str were declared as String and full_txt_str was assigned to a random text string- “Hundred Cars Fifty Cars Ten Cars”. The REPLACE function replaces Cars in this random string with Bicycles, 1 is used to start the replacement from position 1. The final 1 counts the number of occurrences and defines the replacement of the first occurrence only. This new text string is assigned to updated_str with a message box (MsgBox) to show the result.

Press F5.
A message box will be displayed with the new text Bicycles in the first position of Cars.

To replace the first two occurrences of Cars with Bicycles, use the following code.

Sub substitution_of_text_2()
Dim full_txt_str, updated_str As String
full_txt_str = "Hundred Cars Fifty Cars Ten Cars"
updated_str = Replace(full_txt_str, "Cars", "Bicycles", 1, 2)
MsgBox updated_str
End Sub

2 is used as the counting number to replace the first two occurrences of Cars with Bicycles.

Run the code.

This is the output.

Enter the following code to replace all occurrences of the text Cars.

Sub substitution_of_text_2()
Dim full_txt_str, updated_str As String
full_txt_str = "Hundred Cars Fifty Cars Ten Cars"
updated_str = Replace(full_txt_str, "Cars", "Bicycles", 1, 3)
MsgBox updated_str
End Sub

The last argument of the REPLACE function is 3 which is the counting number indicating the replacement of all Cars with Bicycles in the text string.

Press F5.
The following message box will be displayed.

Method 3 – Replacing Text in a Random String with InputBox

Steps:

Follow Step 1 of Method 1.
Use the following code.

Sub substitution_of_text_3()
Dim full_txt_str, new_txt, updated_str As String
full_txt_str = "Hundred Cars Fifty Cars Ten Cars"
new_txt = InputBox("Write down the new text to replace")
updated_str = Replace(full_txt_str, "Cars", new_txt)
MsgBox updated_str
End Sub

full_txt_str, new_txt, and updated_str are declared as String and full_txt_str is assigned to a random text string- “Hundred Cars Fifty Cars Ten Cars”. To have the user-defined input, the InputBox function was used and this value was assigned to new_txt. The REPLACE function replaces Cars with new_txt. The new text string is assigned to updated_str with a message box (MsgBox) to show the result.

Press F5.
Enter your text in the Input Box. Here, Bicycles.
Click OK.

This is the output.

Method 4. Replacing Text in a Range of Strings with Excel VBA

Replace Gmail with the domains in the New Domain column. Insert a new column: Final Email Id.

Steps:

Follow Step 1 in Method 1.
Enter the following code.

Sub substitution_of_text_4()
For i = 4 To 13
If InStr(1, Cells(i, 4).Value, "gmail") > 0 Then
Cells(i, 6).Value = Replace(Cells(i, 4).Value, "gmail", Cells(i, 5).Value)
Else
Cells(i, 6).Value = ""
End If
Next i
End Sub

The FOR loop executes the operation from Row 4 to Row 13. The IF-THEN statement checks whether the email ids of Column D contain “Gmail”. If “Gmail” is found, it will be replaced with the new domains in Column E to create new ids in Column F. Otherwise, a blank will be displayed in Column F.

Press F5.
This is the output.

Method 5- Substituting Text in a Range of Strings with a User Input to Find Text

Replace the following email Ids with the new domains and declare what to replace in the previous Ids with a user input.

Steps:

Follow Step 1 of Method 1.
Enter the following code.

Sub substitution_of_text_5()
Dim partial_text As String
partial_text = Application.InputBox("Enter the string to be replaced")
For i = 4 To 13
If InStr(1, Cells(i, 4).Value, LCase(partial_text)) > 0 Then
Cells(i, 6).Value = Replace(Cells(i, 4).Value, LCase(partial_text), Cells(i, 5).Value)
Else
Cells(i, 6).Value = ""
End If
Next i
End Sub

partial_text is defined as String and assigned to a string that will be provided in the Input Box.
The FOR loop executes the operation from Row 4 to Row 13, and the IF-THEN statement checks whether the email ids of Column D contain “gmail” . If “gmail” is found, it will be replaced with the new domains in Column E to create the new Ids in Column F. Otherwise, a blank will be displayed in Column F.