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

Get FREE Advanced Excel Exercises with Solutions!

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:

VBA Codes to Find and Replace Text in a Column in Excel

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 the 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 replaces 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.

VBA Codes to Find and Replace Text in a Column in Excel

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

VBA Codes to Find and Replace Text in a Column in Excel

Now, we want to replace the TV with Television. Next, click on OK.

VBA Codes to Find and Replace Text in a Column in Excel

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.


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.

VBA Codes to Find and Replace Text in a Column in Excel

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

VBA Codes to Find and Replace Text in a Column in Excel

Here, we want to replace the TV with Television. Next, click on OK.

VBA Codes to Find and Replace Text in a Column in Excel


💬 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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

A.N.M. Mohaimen Shanto
A.N.M. Mohaimen Shanto

A.N.M. Mohaimen Shanto, a B.Sc. in Computer Science and Engineering from Daffodil International University, boasts two years of experience as a Project Manager at Exceldemy. He authored 90+ articles and led teams as a Team Leader, meticulously reviewing over a thousand articles. Currently, he focuses on enhancing article quality. His passion lies in Excel VBA, Data Science, and SEO, where he enjoys simplifying complex ideas to facilitate learning and growth. His journey mirrors Exceldemy's dedication to excellence and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo