Find and replace text in Excel

The following example demonstrates how to search for the first occurrence and all occurrences of some text using using the GemBox.Spreadsheet API in C# and VB.NET applications.

Upload your file (Drag file here)
using System;
using GemBox.Spreadsheet;

class Program
{
    static void Main()
    {
        // If using the Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

        var workbook = ExcelFile.Load("%InputFileName%");
        var worksheet = workbook.Worksheets.ActiveWorksheet;

        // Find first cell with specific text.
        var searchText = "Ranger";
        if (worksheet.Cells.FindText(searchText, out ExcelCell foundCell))
        {
            Console.WriteLine($"First cell with '{searchText}' text:");
            Console.WriteLine($"Name: {foundCell.Name} | Value: \"{foundCell.StringValue}\"");
            Console.WriteLine();
        }

        // Find all cells with specific text.
        searchText = "Apollo";
        Console.WriteLine($"All cells with '{searchText}' text:");

        foreach (var cell in worksheet.Cells.FindAllText(searchText))
            Console.WriteLine($"Name: {cell.Name} | Value: \"{cell.StringValue}\"");

    }
}
Imports System
Imports GemBox.Spreadsheet

Module Program

    Sub Main()

        ' If using the Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")

        Dim workbook = ExcelFile.Load("%InputFileName%")
        Dim worksheet = workbook.Worksheets.ActiveWorksheet

        ' Find first cell with specific text.
        Dim searchText As String = "Ranger"
        Dim foundCell As ExcelCell = Nothing
        If worksheet.Cells.FindText(searchText, foundCell) Then
            Console.WriteLine($"First cell with '{searchText}' text:")
            Console.WriteLine($"Name: {foundCell.Name} | Value: ""{foundCell.StringValue}""")
            Console.WriteLine()
        End If

        ' Find all cells with specific text.
        searchText = "Apollo"
        Console.WriteLine($"All cells with '{searchText}' text:")

        For Each cell In worksheet.Cells.FindAllText(searchText)
            Console.WriteLine($"Name: {cell.Name} | Value: ""{cell.StringValue}""")
        Next

    End Sub
End Module
Excel search with GemBox.Spreadsheet
Screenshot of Excel search with GemBox.Spreadsheet

GemBox.Spreadsheet provides a wide set of methods for searching text that allow you to set search options like case sensitivity and wheter to match entire cell contents or just part of it.

Replace text

With the following example you can find and replace the first occurrence of the desired text, all the occurrences of some text, and all Regex matches of some pattern using one of the AbstractRange.ReplaceText method overloads.

using System.Text.RegularExpressions;
using GemBox.Spreadsheet;

class Program
{
    static void Main()
    {
        // If using the Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

        var workbook = ExcelFile.Load("%#SimpleTemplate.xlsx%");
        var worksheet = workbook.Worksheets.ActiveWorksheet;

        // Replace specific text in first cell in which it occurs.
        string searchText = "Ranger";
        if (worksheet.Cells.FindText(searchText, out ExcelCell foundCell))
            foundCell.ReplaceText(searchText, "REPLACED FIRST");

        // Replace specific text in all cells in which it occurs.
        worksheet.Cells.ReplaceText("Apollo", "REPLACED ALL");

        // Replace specific regex pattern in all cells in which it occurs.
        var searchRegex = new Regex("Luna (\\d{2})");
        worksheet.Cells.ReplaceText(searchRegex, "REPLACED $1");

        workbook.Save("FoundAndReplaced.xlsx");
    }
}
Imports System.Text.RegularExpressions
Imports GemBox.Spreadsheet

Module Program

    Sub Main()

        ' If using the Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")

        Dim workbook = ExcelFile.Load("%#SimpleTemplate.xlsx%")
        Dim worksheet = workbook.Worksheets.ActiveWorksheet

        ' Replace specific text in first cell in which it occurs.
        Dim searchText = "Ranger"
        Dim foundCell As ExcelCell = Nothing
        If worksheet.Cells.FindText(searchText, foundCell) Then
            foundCell.ReplaceText(searchText, "REPLACED FIRST")
        End If

        ' Replace specific text in all cells in which it occurs.
        worksheet.Cells.ReplaceText("Apollo", "REPLACED ALL")

        ' Replace specific regex pattern in all cells in which it occurs.
        Dim searchRegex = New Regex("Luna (\d{2})")
        worksheet.Cells.ReplaceText(searchRegex, "REPLACED $1")

        workbook.Save("FoundAndReplaced.xlsx")

    End Sub
End Module

See also


Next steps

GemBox.Spreadsheet is a .NET component that enables you to read, write, edit, convert, and print spreadsheet files from your .NET applications using one simple API.

Download Buy