Find and Replace in Excel gives us very limited options to work with.

For example, if you look at the image below, you will realize that you can only replace text in Formulas. You can use this option to replace text in Values as well but not Comments.

Now what if you wanted to replace text only in Comments. Well, you can do that easily as explained in this post.

First let’s create a sample Excel file for testing. Create a file as shown below and save it to a location of your choice. I am saving it as C:\Sample.xlsx.

If you notice that in Cell A1 we have the Sum formula. In D1 We have the Comment and C5:C6 we have some Normal Text.

In the example below we will replace:

  • 1) Sum by Max in the formula
  • 2) Siddharth by Sid in the comments
  • 3) Sample by Some other in the normal text

Before we go ahead, let’s first understand the syntax of Cells.Replace.

Let’s Break it up:

Parameters

  • What: Required Object. The data to search for. Can be a string or any Microsoft Excel data type.
  • Replacement: Required Object. The data for replacing. Can be a string or any Microsoft Excel data type.
  • LookAt: Optional Object. Can be one of the following XlLookAt constants: xlWhole or xlPart. Value of constant  xlWhole is 1 and xlPart is 2.
  • SearchOrder: Optional Object. Can be one of the following XlSearchOrder constants: xlByRows or xlByColumns. Value of constant  xlByRows is 1 and xlByColumns is 2.
  • MatchCase: Optional Object. True to make the search case sensitive. The default value is False.
  • MatchByte: Optional Object. Used only if you’ve selected or installed double-byte language support. True to have double-byte characters match only double-byte characters; False to have double-byte characters match their single-byte equivalents.
  • SearchFormat: Optional Object. The search format.
  • ReplaceFormat : Optional Object. The Replace format.


    • Replace Sum by Max in the formula


      The below code will replace Sum by Max in the formula.

      Imports Excel = Microsoft.Office.Interop.Excel
      
      Public Class Form1
          '~~> Define your Excel Objects 
          Dim xlApp As New Excel.Application
          Dim xlWorkBook As Excel.Workbook
          Dim xlWorkSheet As Excel.Worksheet
          Dim xlRange As Excel.Range
      
          Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
              '~~> Define variables to store Find and Replace values
              Dim ToFind As String = "Sum", ToReplace As String = "Max"
      
              '~~> Open the Excel file
              xlWorkBook = xlApp.Workbooks.Open("C:\Sample.xlsx")
      
              '~~> Sheet where you want to do a replace
              xlWorkSheet = xlWorkBook.Sheets("Sheet1")
      
              '~~> Display Excel
              xlApp.Visible = True
      
              '~~> Set your range
              xlRange = xlWorkSheet.Range("A1:A10")
      
              xlRange.Replace(What:=ToFind, Replacement:=ToReplace, LookAt:=Excel.XlLookAt.xlPart, _
              SearchOrder:=Excel.XlSearchOrder.xlByRows, MatchCase:=False, SearchFormat:=False, _
              ReplaceFormat:=False)
          End Sub
      End Class
      

      After you run the above code, you will notice that the Sum has been replaced by Max in the formula.


      Replace Siddharth by Sid in the comments


      The below code will replace Siddharth by Sid in the comments. In the below code, I am looping through all the cells in the sheet which has comments and then searching for the text which needs to be replaced.

      Imports Excel = Microsoft.Office.Interop.Excel
      
      Public Class Form1
          '~~> Define your Excel Objects 
          Dim xlApp As New Excel.Application
          Dim xlWorkBook As Excel.Workbook
          Dim xlWorkSheet As Excel.Worksheet
          Dim xlRange As Excel.Range
      
          Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
              '~~> Define variables to store Find and Replace values
              Dim ToFind As String = "Siddharth", ToReplace As String = "Sid"
      
              '~~> Open the Excel file
              xlWorkBook = xlApp.Workbooks.Open("C:\Sample.xlsx")
      
              '~~> Sheet where you want to do a replace
              xlWorkSheet = xlWorkBook.Sheets("Sheet1")
      
              '~~> Display Excel
              xlApp.Visible = True
      
              xlRange = xlWorkSheet.UsedRange.SpecialCells(Excel.XlCellType.xlCellTypeComments)
      
              Dim aCell As Excel.Range
      
              For Each aCell In xlRange.Cells
                  aCell.Comment.Text(xlApp.Substitute(aCell.Comment.Text, _
                  ToFind, ToReplace))
              Next aCell
          End Sub
      End Class
      

      After you run the above code, you will notice that Siddharth has been replaced by Sid in the comments.


      Replace Sample by Some other in the normal text


      The code for replacing Sample by Some other in the normal text is the same as what we did for the replacing text in a formula.

      Imports Excel = Microsoft.Office.Interop.Excel
      
      Public Class Form1
          '~~> Define your Excel Objects 
          Dim xlApp As New Excel.Application
          Dim xlWorkBook As Excel.Workbook
          Dim xlWorkSheet As Excel.Worksheet
          Dim xlRange As Excel.Range
      
          Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
              '~~> Define variables to store Find and Replace values
              Dim ToFind As String = "Sample", ToReplace As String = "Some other"
      
              '~~> Open the Excel file
              xlWorkBook = xlApp.Workbooks.Open("C:\Sample.xlsx")
      
              '~~> Sheet where you want to do a replace
              xlWorkSheet = xlWorkBook.Sheets("Sheet1")
      
              '~~> Display Excel
              xlApp.Visible = True
      
              '~~> Set your range
              xlRange = xlWorkSheet.Range("C5:C6")
      
              xlRange.Replace(What:=ToFind, Replacement:=ToReplace, LookAt:=Excel.XlLookAt.xlPart, _
              SearchOrder:=Excel.XlSearchOrder.xlByRows, MatchCase:=False, SearchFormat:=False, _
              ReplaceFormat:=False)
          End Sub
      End Class
      

      Caution: Be careful when you are replacing the values. Identify the range where you want to replace and use all parameters of the replace function. Especially LookAt and MatchCase.