If you are looking for ways to replace text in a string using Excel VBA, then you will find this article useful. Replacing a certain text part may save a lot of time in typing the text strings again. So, let’s get into the main article to know the details about this replacement task.
Here, we have the following dataset containing some records of the employees with their email ids. Our task is to replace the old domain names with the new ones. In the following methods, we will work with this dataset along with some random text strings to replace desired text with VBA codes.
We have used the Microsoft Excel 365 version here, you can use any other version according to your convenience.
1. Using Excel VBA to Replace Text Starting from the n-th Position of a Random String
Here, we will replace text in a random text string for different starting positions.
Step-01:
➤ Go to the Developer Tab >> Code Group >> Visual Basic Option.
Then, the Visual Basic Editor will open up.
➤ Go to the Insert Tab >> Module Option.
After that, a Module will be created.
Step-02:
➤ Write 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 SubHere, we have declared full_txt_str and updated_str as String and then assigned full_txt_str to a random text string- “Hundred Cars Fifty Cars Ten Cars”. Then the VBA REPLACE function is used to replace the Cars part of this random string with Bicycles and 1 is used here to start the replacement from position 1 of this string. Finally, we have assigned this new text string to updated_str and with a message box (MsgBox) we will see the result.
➤ Press F5.
Then a message box will appear with the new text string with the replaced text Bicycles.
➤ To do the replacement process from the second instance of Cars 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 SubHere, we have used the starting position as 14 because we want to have the part of the string after Hundred Cars and replace the Cars here.
➤ After running the code, we will have the following message box with the text string starting from the text Fifty and with Bicycles in the position of Cars.
➤ For having only the last portion of this string we are applying 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 SubHere, we have used the starting position as 25 because we want to have the part of the string after Fifty Cars and replace the Cars with Bicycles here.
Finally, we will have a message box with our desired part of the string having the replacement with Bicycles.
Read More: Excel VBA to Replace Blank Cells with Text
2. Substituting Text for n-th Occurrence of a Random String with VBA
In this section, we will replace text in a random string for different numbers of occurrences with the help of a VBA code.
Steps:
➤ Follow Step-01 of Method-1.
➤ Type 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 SubHere, we have declared full_txt_str and updated_str as String and then assigned full_txt_str to a random text string- “Hundred Cars Fifty Cars Ten Cars”. After that, the REPLACE function is used to replace the Cars part of this random string with Bicycles, 1 is used here to start the replacement from position 1 of this string, and the final 1 is for counting the number of occurrences. By using 1 as the counting number we are defining the replacement of the first Cars only. Finally, we have assigned this new text string to updated_str and with a message box (MsgBox) we will see the result.
➤ Press F5.
Afterward, a message box will appear with the new text Bicycles in the first position of Cars only.
➤ For replacing the first two instances 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 SubHere, 2 is used as the counting number to replace the first two instances of Cars with Bicycles.
After running the code, you will have the replacement of the first two texts Cars with Bicycles.
➤ Apply the following code to replace all of the instances 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 SubHere, the last argument of the REPLACE function is 3 which is the counting number indicating the replacement of all of the Cars with Bicycles in the text string.
➤ Press F5.
Afterward, we will have the following message box with the replaced text Bicycles in the string.
3. Replacing Text in a Random String with InputBox
Here, we will replace a certain text of a random string with a text that will be defined by a user with the help of the VBA InputBox function.
Steps:
➤ Follow Step-01 of Method-1.
➤ Type 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 SubHere, we have declared full_txt_str, new_txt, and updated_str as String and then assigned full_txt_str to a random text string- “Hundred Cars Fifty Cars Ten Cars”. To have the user-defined input as the text to be replaced with the Cars in the random string, we have used the InputBox function and then assigned this value to new_txt. Then the REPLACE function is used to replace the Cars part of this random string with new_txt. Finally, we have assigned this new text string to updated_str and with a message box (MsgBox) we will see the result.
➤ Press F5.
After that, an Input Box will appear where you can enter any text part that you want to have in the new string.
➤ Type Bicycles or any other text you want and then press OK.
Finally, you will have the following result with the new text string having new text Bicycles in the position of Cars.
4. Replacing Text in a Range of Strings with Excel VBA
Here, we will substitute the Gmail part of the email IDs with the domains in the New Domain column, and to accumulate the new email IDs we have inserted a new column; Final Email Id.
Steps:
➤ Follow Step-01 of Method-1.
➤ Type 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 SubHere, we have used the FOR loop to execute the operation from Row 4 to Row 13. With the help of the IF-THEN statement, we have checked whether the email ids of Column D contain “Gmail” or not, and for fulfilling this criterion the “Gmail” portion of the email ids will be replaced with the new domains of Column E to create the new ids in Column F. Otherwise, you will have a blank in the corresponding cells of Column F.
➤ Press F5.
Then, you will have the new email Ids in the Final Email Id column.
5. Substituting Text in a Range of Strings with User Input to Find Text
You can replace the following email Ids with the new domains and declare what to replace in the previous Ids a user input can be used by following this method.
Steps:
➤ Follow Step-01 of Method-1.
➤ Type 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 SubHere, we have defined partial_text as a String and then assigned it to a string that will be given by a user through the Input Box.
Afterward, we used the FOR loop to execute the operation from Row 4 to Row 13, and using the IF-THEN statement, we checked whether the email ids of Column D contain “gmail” or not. To fulfill this criterion the “gmail” portion of the email Ids will be replaced with the new domains of Column E to create the new Ids in Column F. Otherwise, you will have a blank in the corresponding cells of Column F.
➤ Press F5.
After that, you will have an Input Box where you have to type the text which you want to search in the range of email ids (here we have entered gmail) and then press OK.
Finally, we have our updated email IDs in the Final Email Id column.
Practice Section
For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.
Download Workbook
Conclusion
In this article, we tried to cover the ways to replace text in a string using Excel VBA. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.
