Replace the substring in a formula in multiple cells at once in a Sheet

Aspose.Cells Product Family
1

hi,
Is there a way a straight forward method to replace all the substring at once in a sheet, so that I can replace all the substrings in a formula “=IFERROR((AVERAGEIFS(Data!$AL$2:Data!ALTEST,Data!$HL$2:Data!HLTEST,A4,Data!$AL$2:Data!ALTEST,”<>")),0)" like “TEST” to “30”. There are thousands of cells with formula.

I know, I can do it by grabbing each cell and replacing it, but it is taking a lot of time to process
Currently using the below code to replace individual cell in c#

Cells helperCells = _SourceWorkbook.Worksheets[“Sheet1”].Cells;
int cnt = 0;
string helper_ftext = null;

                for (IEnumerator ie = helperCells.GetEnumerator(); ie.MoveNext();)
                {
                    Cell cell = (Cell)ie.Current;

                    if (cell.IsFormula)
                    {
                        helper_ftext = cell.Formula.Replace("TEST", (DatarowsCount + 1).ToString());
                        // Apply the formula to particular cell
                        helperCells[cell.Name].SetArrayFormula(helper_ftext, 1, 1);
                        cnt++;
                    }
                }

Appreciate if anyone can help.
Thanks

3

Hi @kiran5388.

There is method to do that in the way that you have mentioned. However, you can create multiple threads and let each thread change the formula in a cell subset. Please make sure to set Worksheet.Cells.MultiThreadReading to true. If you do not, you might get the wrong cell values.

Let me know if it helps.

4

@kiran5388,

According to your sample formula it seems what you want to change is the text of defined name in references. If so, the most efficient and simple way is renaming the corresponding names, such as, renaming Data!ALTEST to Data!ALXXXX, renaming Data!HLTEST to Data!HLXXXX, …etc. After you renaming those names, the references in the formula will be updated automatically.

However, AL30 and HL30 is cell address so it cannot be set as the text of defined names. If your purpose is just to change those names to cell addresses, we think you can modify the defined names to set the ReferTo to corresponding addresses. For example: Name.ReferTo = “=Data!AL30” for Data!ALTEST, …etc.