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.

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

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

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

📌 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 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)