In Microsoft Excel, a VBA macro can solve a lot of problems with ease. The problem you were solving with complexity, VBA will do that with less time. In our day-to-day Excel task, we perform replacing text from a worksheet. And this is a very familiar action that you will see. We do this on various occasions. In this tutorial, you will learn to use the VBA to find and replace text in a column in Excel.
This tutorial will be on point with suitable examples and proper illustrations. So, read the whole article to develop your knowledge.
Download Practice Workbook
Download this practice workbook.
2 VBA Codes to Find and Replace Text in a Column in Excel
In the following sections, we are going to provide you with two VBA macros to find and replace text in a column in Excel. I recommend you learn and apply these methods in your worksheet. It will surely develop your Excel knowledge.
To demonstrate this tutorial, we are using this dataset:
Here, we have a dataset of some salespersons, their selling products, and their prices. Now, we will use the VBA macros to find and replace some products from the dataset. Let’s get into it.
1. Use Replace Method of VBA to Find and Replace Text in a Column
Now, to find and replace text in a column, the VBA Replace method is the most used. This method searches and replace specific texts from the cells of a column. You may have multiple texts that you want to replace in a column. This method will do that with ease for you. It should be your go-to method.
A. Build the Code
📌 Open VBA Editor
- First, press Alt+F11 on your keyboard to open the VBA editor.
- Then, select Insert>Module.
📌 Create Subprocedure
Sub find_and_replace()
End Sub
This is our subprocedure. We will write all the codes inside this.
📌 Declare Necessary Variables
Sub find_and_replace()
Dim find_value, replace_value As String
End Sub
Find_value: This variable will contain the text that you to replace.
replace_value: This variable will contain the new text.
📌 Take User Inputs
Sub find_and_replace()
Dim find_value, replace_value As String
find_value = InputBox("Which Product You Want to Replace?")
replace_value = InputBox("Please Enter the New Value")
End Sub
Here, we used two input boxes that will take the inputs from the users and store them in the variables.
📌 Replace the Text
Sub find_and_replace()
Dim find_value, replace_value As String
find_value = InputBox("Which Product You Want to Replace?")
replace_value = InputBox("Please Enter the New Value")
Columns("C").replace What:=find_value, _
Replacement:=replace_value, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
End Sub
Here, we used the Replace method of VBA to find and replace the text.
Columns(“C”): We want to find and replace text from the Product column. You can see the Product column is the column C in our Excel sheet.
What:=find_value: The text you want to replace.
Replacement:=replace_value: Your new text value.
LookAt:=xlPart: It will look for the value and match against your searched text.
SearchOrder:=xlByRows: It searches your text row by row in the column.
MatchCase:=False: It makes your search case-insensitive. It will match the text in both uppercase and lowercase.
📌 Show Finished Message
Sub find_and_replace()
Dim find_value, replace_value As String
find_value = InputBox("Which Product You Want to Replace?")
replace_value = InputBox("Please Enter the New Value")
Columns("C").replace What:=find_value, _
Replacement:=replace_value, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
MsgBox "Done With Replacement!"
End Sub
Finally, it will show you a finished message.
B. Run the Code
We have built the code. Now, it’s time to check the code whether the code is working or not.
First, press Alt+F8 to open the macro dialog box.
Now, select find_and_replace and click on Run.
Now, provide the text you want to replace. Here, we want to replace the TV from the product column. After that, click on OK.
Now, we want to replace the TV with Television. Next, click on OK.
Finally, you can see our VBA code successfully worked to find and replace the text from the column in the Excel sheet. Also, you can see a message box after the replacement.
Read More: How to Replace Text in Excel Formula (7 Easy Ways)
Similar Readings:
- How to Find and Replace Asterisk (*) Character in Excel
- Find and Replace Values in Multiple Excel Files (3 Methods)
- How to Replace Text in Selected Cells in Excel (4 Simple Methods)
- Use the Substitute Function in Excel VBA (3 Examples)
- Excel VBA: How to Find and Replace Text in Word Document
2. Use Loop to Find and Replace Text in a Column in Excel
Now, this VBA macro is an optional method. You can create a loop that will loop through your given column. After that, it will find and replace the specific text from the column. The following breakdown will let you understand the code easily.
A. Build the Code
📌 Open VBA Editor
- First, press Alt+F11 on your keyboard to open the VBA editor.
- Then, select Insert>Module.
📌 Create Subprocedure
Sub find_and_replace_loop()
End Sub
This is our subprocedure. We will write all the codes inside this.
📌 Declare Necessary Variables
Sub find_and_replace_loop()
Dim find_value, replace_value As String
End Sub
Find_value: This variable will contain the text that you want to replace.
replace_value: This variable will contain the new text.
📌 Take User Inputs
Sub find_and_replace_loop()
Dim find_value, replace_value As String
On Error Resume Next
find_value = InputBox("Which Product You Want to Replace?")
replace_value = InputBox("Please Enter the New Value")
End Sub
Here, we used two input boxes that will take the inputs from the users and store them in the variables.
On Error Resume Next: It is a method to handle errors. On Error Resume Next ignores the errors and proceeds to the next line.
📌 Create Loop and Replace the Text in the Column
Sub find_and_replace_loop()
Dim find_value, replace_value As String
On Error Resume Next
find_value = InputBox("Which Product You Want to Replace?")
replace_value = InputBox("Please Enter the New Value")
For Each rng In Range("C5:C18")
If rng.Value = find_value Then
rng.Value = replace_value
End If
Next
End Sub
For Each rng In Range(“C5:C18”): We are running a loop in the product column. We took the range of cells that have values.
If rng.Value = find_value: It checks whether the current cell value matches the user’s given value.
rng.Value = replace_value: If matches, it sets the current cell value with the new value
📌 Show Finished Message
Sub find_and_replace_loop()
Dim find_value, replace_value As String
On Error Resume Next
find_value = InputBox("Which Product You Want to Replace?")
replace_value = InputBox("Please Enter the New Value")
For Each rng In Range("C5:C18")
If rng.Value = find_value Then
rng.Value = replace_value
End If
Next
MsgBox "Done With Replacement!"
End Sub
Finally, it will show you a finished message.
B. Run the Code
We have built the code. Now, it’s time to check whether the code is working or not.
First, press Alt+F8 to open the macro dialog box.
Next, select find_and_replace_loop and click on Run.
After that, provide the text you want to replace. Here, we want to replace the TV from the product column. Now, click on OK.
Here, we want to replace the TV with Television. Next, click on OK.
Read More: How to Find and Replace Using Formula in Excel (4 Examples)
💬 Things to Remember
✎ We made the VBA Replace case-insensitive. You can set the value to True to find and replace the exact match.
✎ You can change the column according to your Excel sheet.
✎ One thing to remember, using the loop makes your code slightly slower. If you have a large dataset, it will be wise to use the Replace method instead of the loop.
Conclusion
To conclude, I hope this tutorial has provided you with a piece of useful knowledge to find and replace text in a column in Excel using VBA codes. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.
Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions.
Keep learning new methods and keep growing!
Related Articles
- Replace Text of a Cell Based on Condition in Excel (5 Easy Methods)
- How to Find and Replace within Selection in Excel (7 Methods)
- Find and Replace Multiple Words at Once in Excel (7 Methods)
- How to Find and Replace from List with Macro in Excel (5 Examples)
- Excel VBA to Replace Blank Cells with Text (3 Examples)
- How to Replace Text after Specific Character in Excel (3 Methods)
- How to Replace Text between Two Characters in Excel (3 Easy Ways)