Greetings, I'm currently running an excel script where I am replacing all single quote characters with two single quote characters consecutively so that I can push the data in a table over to an SQL server utilizing Power Automate. One issue that I am experiencing is that when utilizing the below script it also replaces the characters inside of formulas:// Replace SQL Reserved Characters selectedSheet.replaceAll("'", "''", { completeMatch: false, matchCase: false }) Example of how the formula in one of the cells originally existed:=XLOOKUP(E4,'Sheet2'!E:E,'Sheet2'!F:F)Example of how the formulas get formatted after running the script:=XLOOKUP(E4,''Sheet2''!E:E,''Sheet2''!F:F) Is there anyway to get the replaceAll function to ignore characters inside of formulas? I'm trying to eliminate the need to individually iterate through each cell as this is resource intensive. I also thought about just copying the range and pasting back the values only, but I feel this will also be resource intensive.

Lorenzo That doesn't work such way. Selecting special cells we split entire range on range areas, replaceAll can't be applied to RangeAreas object. However, idea is great. We may iterate each area and make replacements within it function main(workbook: ExcelScript.Workbook) { workbook .getActiveWorksheet() .getUsedRange() .getSpecialCells(ExcelScript.SpecialCellType.constants) .getAreas() .map(a => a.getUsedRange().replaceAll("'", "''", {})) } Iterating cell by cell in initial range is less preferable from performance point of view.

Lorenzo I'm surprized you generated such solution without Automate on the board. Just in case. Since that will be used with Power Automate which has no idea about active worksheet, we need to define the worksheet explicitly; or iterate all worksheets if replacements shall be done in each of them. Like function main(workbook: ExcelScript.Workbook) { workbook .getWorksheets() .forEach(sheet => ( sheet.getUsedRange() !== undefined ) ? sheet .getUsedRange() .getSpecialCells(ExcelScript.SpecialCellType.constants) .getAreas() .map(a => a.getUsedRange().replaceAll("'", "''", {})) : '' ) } In addition we check if the sheet is empty and skip it, otherwise script stops on it.

Lorenzo We may use console.log here, but no sense if the script runs by Power Automate. You variant returns ${sheet} has no UsedRange if change on console.log( sheet.getName() + ' is empty') it returns something like Sheet3 is empty === Yes, without FALSE condition it returns Line #13: ':' expected. However, if instead of shortened "if" to use regular "if" we may skip it function main(workbook: ExcelScript.Workbook) { workbook .getWorksheets() .forEach(sheet => { if( sheet.getUsedRange() !== undefined ) { sheet .getUsedRange() .getSpecialCells(ExcelScript.SpecialCellType.constants) .getAreas() .map(a => a.getUsedRange().replaceAll("'", "''", {})) } } ) }

Hi djmitz6 Post deleted: misunderstand on how this works - refer to 1st post from SergeiBaklan below please

djmitz6 Here's how you can modify your Excel Script to replace all single quotes while ignoring characters inside formulas:Option 1: Using FilterBy and ReplaceAll:Filter formulas: Use filterBy to filter out rows containing formulas:JavaScriptconst filteredRange = selectedSheet.getRange("A1:B10").filterBy(row => !row.getRange("A1").hasFormula()); Replace A1:B10 with the actual range you want to filter and A1 with the cell containing the formula you want to check.Replace single quotes: Apply replaceAll to the filtered range:JavaScriptfilteredRange.replaceAll("'", "''", { completeMatch: false, matchCase: false }); Optionally, replace back in the original range:JavaScript// If needed, replace back in the original range after modification selectedSheet.getRange("A1:B10").values = filteredRange.getValues(); Option 2: Using a custom function:Define a custom function:JavaScriptfunction replaceExceptInFormulas(text, searchValue, replaceValue) { // Split the text into parts based on formulas const parts = text.split(/={1,2}[^=]+={0,2}/); // Replace in non-formula parts for (let i = 0; i < parts.length; i++) { if (!parts[i].startsWith("=")) { parts[i] = parts[i].replaceAll(searchValue, replaceValue); } } // Join the parts back together return parts.join(""); } Apply the function:JavaScriptselectedSheet.getRange("A1:B10").values = selectedSheet.getRange("A1:B10").values.map(row => row.map(cell => replaceExceptInFormulas(cell, "'", "''"))); Explanation:Option 1: This approach filters out rows containing formulas using filterBy before applying replaceAll to avoid modifying formulas.Option 2: This option defines a custom function replaceExceptInFormulas that splits the text into parts based on formulas and replaces characters only in non-formula parts.Both options achieve the desired result of replacing single quotes while preserving formulas. Choose the option that best suits your preference and coding style.Additional notes:Ensure you replace A1:B10 with the actual range you want to modify.Adjust the formula checking logic in Option 1 if your formulas have different patterns.Consider testing the script on a small sample of data before applying it to your entire dataset.

Excel Script ReplaceAll ignore formulas

Lorenzo
Silver Contributor
Feb 22, 2024
Hi djmitz6

~~Post deleted~~: misunderstand on how this works - refer to 1st post from SergeiBaklan below please
- SergeiBaklan
  MVP
  Feb 22, 2024
  Lorenzo
  
  That doesn't work such way. Selecting special cells we split entire range on range areas, replaceAll can't be applied to RangeAreas object.
  
  However, idea is great. We may iterate each area and make replacements within it
  
  function main(workbook: ExcelScript.Workbook) { workbook .getActiveWorksheet() .getUsedRange() .getSpecialCells(ExcelScript.SpecialCellType.constants) .getAreas() .map(a => a.getUsedRange().replaceAll("'", "''", {})) }
  
  Iterating cell by cell in initial range is less preferable from performance point of view.
  - Lorenzo
    Silver Contributor
    Feb 22, 2024
    SergeiBaklan
    
    Selecting special cells we split entire range on range areas, replaceAll can't be applied to RangeAreas object
    OK, I didn't read enough. Thanks for fixing my bad + sharing the appropriate code
smylbugti222gmailcom
Iron Contributor
Feb 22, 2024
djmitz6
Here's how you can modify your Excel Script to replace all single quotes while ignoring characters inside formulas:
Option 1: Using FilterBy and ReplaceAll:
Filter formulas: Use filterBy to filter out rows containing formulas:
JavaScript
const filteredRange = selectedSheet.getRange("A1:B10").filterBy(row => !row.getRange("A1").hasFormula());

Replace A1:B10 with the actual range you want to filter and A1 with the cell containing the formula you want to check.
Replace single quotes: Apply replaceAll to the filtered range:
JavaScript
filteredRange.replaceAll("'", "''", { completeMatch: false, matchCase: false });

Optionally, replace back in the original range:
JavaScript
// If needed, replace back in the original range after modification selectedSheet.getRange("A1:B10").values = filteredRange.getValues();

Option 2: Using a custom function:
Define a custom function:
JavaScript
function replaceExceptInFormulas(text, searchValue, replaceValue) { // Split the text into parts based on formulas const parts = text.split(/={1,2}[^=]+={0,2}/); // Replace in non-formula parts for (let i = 0; i < parts.length; i++) { if (!parts[i].startsWith("=")) { parts[i] = parts[i].replaceAll(searchValue, replaceValue); } } // Join the parts back together return parts.join(""); }

Apply the function:
JavaScript
selectedSheet.getRange("A1:B10").values = selectedSheet.getRange("A1:B10").values.map(row => row.map(cell => replaceExceptInFormulas(cell, "'", "''")));

Explanation:
Option 1: This approach filters out rows containing formulas using filterBy before applying replaceAll to avoid modifying formulas.
Option 2: This option defines a custom function replaceExceptInFormulas that splits the text into parts based on formulas and replaces characters only in non-formula parts.
Both options achieve the desired result of replacing single quotes while preserving formulas. Choose the option that best suits your preference and coding style.
Additional notes:
Ensure you replace A1:B10 with the actual range you want to modify.
Adjust the formula checking logic in Option 1 if your formulas have different patterns.
Consider testing the script on a small sample of data before applying it to your entire dataset.

Forum Discussion

Excel Script ReplaceAll ignore formulas

Share

Resources