Tuesday, 17 April 2012 08:25

Find and Replace-Find and Replace Data in Excel

Written by  support iceblue
Rate this item
(0 votes)
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: Find and Replace Data in Excel with C#, VB.NET

 

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

            foreach (CellRange range in ranges)
            {
                range.Text = "Santa Monica";
                range.Style.Color = Color.Yellow;
            }
          
[VB.NET]
        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



Spire.XLS is a powerful Excel component which enables developers/programmers to fast generate, open, view, edit, save and print Excel files for .NET, Silverlight and WPF. It supports C#, VB.NET, ASP.NET, ASP.NET MVC and Silverlight.

 

About .NET Excel Component

Additional Info

  • tutorial_title: Find and Replace Data
Last modified on Wednesday, 08 January 2014 02:51