Excel VBA to Find and Replace Text in a Column: 2 Methods

Method 1 – Use the Replace Method of VBA to Find and Replace Text in a Column

A. Build the Code

Open VBA Editor

Press Alt+F11 on your keyboard to open the VBA editor.
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

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

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. 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 it 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. It’s time to check the code whether the code is working or not.

Press Alt+F8 to open the macro dialog box.

Select find_and_replace and click Run.

Provide the text you want to replace. Here, we want to replace the TV from the product column. After that, click OK.

Replace the TV with Television. Click OK.

See our VBA code successfully worked to find and replace the text from the column in the Excel sheet. See a message box after the replacement.

Method 2 – Use Loop to Find and Replace Text in a Column in Excel

A. Build the Code

Open VBA Editor

Press Alt+F11 on your keyboard to open the VBA editor.
Select Insert>Module.

Create Subprocedure

Sub find_and_replace_loop()

End Sub

This is our subprocedure. 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

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 run 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 it 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. Check whether the code is working.

First, press Alt+F8 to open the macro dialog box.

Select find_and_replace_loop and click on Run.

Provide the text you want to replace. Replace the TV from the product column. Click OK.

Replace the TV with Television. Click OK.

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.

Find and Replace Text in Column with VBA.xlsm