Find and Replace Data in Excel

Find and Replace in Excel allows users to replace text/number in one or more cells with other information after finding. Find and Replace functions saves users much time to get some specified data in cells and replace it with updated information.

Spire.XLS for .NET, a professional component to manipulate Excel documents for .NET, enables users to find and replace data in Excel by using C#, VB.NET. The following screenshot shows the original Excel file and information in orange boxes will be replaced with other information.

Find and Replace Excel Data

With Spire.XLS for .NET, use sheet.FindAllString(data string, bool formula, bool formulaValue) method to find data which you want to replace and save it in a CellRange array. Then, use foreach sentence to get each item in this array and set text and color for the items.Download and install Spire.XLS for .NET and use the following code to find and replace data in Excel.

Sample Code:

CellRange[] ranges = sheet.FindAllString("Huntington Beach", false, false);

            foreach (CellRange range in ranges)
                range.Text = "Santa Monica";
                range.Style.Color = Color.Yellow;
        Dim ranges() As CellRange = sheet.FindAllString("Huntington Beach", False, False)

            For Each range As CellRange In ranges
                range.Text = "Santa Monica"
                range.Style.Color = Color.Yellow
            Next range

After running, you can get the following result.

Find and Replace Excel Data