Replace method seems to be looking for the text in the entire cell. Cant I replace part of the text within a cell? or am I missing something?
Kiran
Hi Kiran,
You can try the following approach:
//Find cell text contains “abc” and replace it with "def"
Cells cells = excel.Worksheets[0].Cells;
Cell cell = null;
for(;
{
cell = cells.FindStringContains(“abc”);
if(cell == null)
break;
string text = cell.StringValue;
cell.PutValue(text.Replace(“abc”, “def”));
}
Thanks Laurence. This worked great. I have 1 more issue though. I need to look for strings within a cell which starts with “a” & ends with “z” (there can be multiple strings within a cell.) Is there a way I could accomplish this?
If Aspose.Excel supported regular expression it would have been great.
thanks
Kiran
We will investigate the regular expression support.
And I think now it's also easy to meet your need. The following is the sample code:
for(int i = 0; i < cells.Count; i ++)
{
Cell cell = cells;
if(cell.Type == CellValueType.String)
{
string text = cell.StringValue;
//The you can use your regular expression to check the text
..............
}
}
@kiran,
Aspose.Excel is deprecated and no more active development is done for it. A new product Aspose.Cells has replaced Aspose.Excel and supports all the latest features of MS Excel. You can use Aspose.Cells to find and replace text in a variety of ways. Here is an example to use this feature:
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// Instantiate the workbook object
Workbook workbook = new Workbook(sourceDir + "sampleFindingDataOrFormulasUsingFindOptions.xlsx");
workbook.CalculateFormula();
// Get Cells collection
Cells cells = workbook.Worksheets[0].Cells;
// Instantiate FindOptions Object
FindOptions findOptions = new FindOptions();
// Create a Cells Area
CellArea ca = new CellArea();
ca.StartRow = 8;
ca.StartColumn = 2;
ca.EndRow = 17;
ca.EndColumn = 13;
// Set cells area for find options
findOptions.SetRange(ca);
// Set searching properties
findOptions.SearchBackward = false;
findOptions.SeachOrderByRows = true;
// Set the lookintype, you may specify, values, formulas, comments etc.
findOptions.LookInType = LookInType.Values;
// Set the lookattype, you may specify Match entire content, endswith, starwith etc.
findOptions.LookAtType = LookAtType.EntireContent;
// Find the cell with value
Cell cell = cells.Find(341, null, findOptions);
if (cell != null)
{
Console.WriteLine("Name of the cell containing the value: " + cell.Name);
}
else
{
Console.WriteLine("Record not found ");
You may follow below links for more details about searching text:
Finding Cells Containing Specified String Value or Number
Find or Search Data
Replace text in a workbook using Regular Expression
The latest version of this new product can be downloaded here:
Aspose.Cells for .NET(Latest version)
Here is a runnable complete solution which contains lot of examples to test different features of Aspose.Cells.