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.
using GemBox.Spreadsheet;
using System;
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 GemBox.Spreadsheet
Imports System
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
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. 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 Replace text
AbstractRange.ReplaceText method overloads.using GemBox.Spreadsheet;
using System.Text.RegularExpressions;
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 GemBox.Spreadsheet
Imports System.Text.RegularExpressions
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
