Click or drag to resize

Find and Replace in Excel Spreadsheets with ExpertXls

The Find and Replace methods of ExcelRange, ExcelWorksheet and ExcelWorkbook search a range, a worksheet or all the worksheets of a workbook. By default the text is matched in any part of the cell and without regard to case; the ExcelFindOptions flags match the case and/or the entire cell content.

  • Find returns the cells that display the text, as single cell ranges: text constants, numbers and dates as they are displayed with their number format, and the results of the formulas. Call UpdateFormulaResult first to calculate the formulas added in code.

  • Replace changes only the cells that contain text and returns the number of changed cells; formulas, numbers, dates and booleans are not changed. With MatchEntireCellContent the cell text is replaced when it is equal to the old text, otherwise every occurrence of the old text in the cell is replaced.

Finding and Replacing Text
// the cells of a worksheet that contain "pdf", in any case
ExcelRange[] pdfCells = worksheet.Find("pdf");
foreach (ExcelRange cell in pdfCells)
    cell.Style.Font.Bold = true;

// the cells of all the worksheets that contain "Excel", matching the case
ExcelRange[] excelCells = workbook.Find("Excel", ExcelFindOptions.MatchCase);

// replace "North" only where it is the whole cell text
int regions = workbook.Replace("North", "North America", ExcelFindOptions.MatchEntireCellContent);

// replace a text in a range, in any part of the cells
int count = worksheet["A7:A18"].Replace("Image Extractor", "PDF Image Extractor");
See Also