Excel VBA to Find and Replace Text in a Column (2 Examples)

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.

Excel VBA Find and Replace Text in Column: 2 VBA Codes

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

📌 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

📌 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

📌 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

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.

Similar Readings:

• How to Find and Replace Asterisk (*) Character in Excel
• How to Find and Replace Values in Multiple Excel Files
• Excel VBA: How to Find and Replace Text in Word Document
• Find and Replace a Text in a Range with Excel VBA
• How to Find and Replace Multiple Values in Excel with VBA

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

📌 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

📌 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

📌 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

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 in Excel Column

💬 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.

Download Practice Workbook

Download this practice workbook.

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.

Keep learning new methods and keep growing!

Related Articles

• How to Find and Replace within Selection in Excel
• How to Find and Replace from List with Macro in Excel
• How to Find and Replace Using Formula in Excel
• How to Find and Replace Values Using Wildcards in Excel
• Find and Replace Tab Character in Excel
  
• Find And Replace Multiple Values in Excel
• How to Find and Replace Multiple Words from a List in Excel
• How to Find and Replace Text Color in Excel
• [Fixed!] Excel Find and Replace Not Working