Forum Discussion
Solved
Clean function as script
Does anyone know how to use the clean function within an excel script? I need to use it on a defined range (g4:g2500), repetitively. If so, I'd be grateful to see your script code.
As variant that could be like
function main(workbook: ExcelScript.Workbook) { const range = workbook .getWorksheet("Sheet1") .getRange("D2:D10") const texts = range .getTexts() .map(s => Array.of( s.toString() .replace(/[\x00-\x1F]/g, '') ) ) workbook .getWorksheet("Sheet1") .getRange("E2:E10") .setValues( texts ) }which removes from the string all non-printable character with ASCII codes from 0 to 31, what the CLEAN() does.
You may add other non-printable characters, for example if to add non-breaking space (code 160 or \xA0) replace will be
.replace(/[\x00-\x1F,\xA0]/g, '')Thus you may create your own, more powerful CLEAN()
As variant that could be like
function main(workbook: ExcelScript.Workbook) { const range = workbook .getWorksheet("Sheet1") .getRange("D2:D10") const texts = range .getTexts() .map(s => Array.of( s.toString() .replace(/[\x00-\x1F]/g, '') ) ) workbook .getWorksheet("Sheet1") .getRange("E2:E10") .setValues( texts ) }which removes from the string all non-printable character with ASCII codes from 0 to 31, what the CLEAN() does.
You may add other non-printable characters, for example if to add non-breaking space (code 160 or \xA0) replace will be
.replace(/[\x00-\x1F,\xA0]/g, '')Thus you may create your own, more powerful CLEAN()
- Thank you. Your solution works!!
Bkkendig , you are welcome
To use the CLEAN function within an Office Script in Excel, you can follow these steps:
- Open your Excel workbook.
- Click on the "Automate" tab in the ribbon.
- Click on "Script Lab" to open the Office Scripts editor.
- In the Office Scripts editor, create a new script or open an existing one.
- Write the following code to apply the CLEAN function to the defined range:
function cleanRange() { // Get the defined range const range = context.workbook.getRange("Sheet1!G4:G2500"); // Load the range values range.load("values"); return context.sync() .then(() => { // Apply CLEAN function to each cell in the range range.values = range.values.map(row => row.map(cell => cell ? Office.Script.Excel.Functions.clean(cell) : null)); return context.sync(); }); }- Save the script and close the Office Scripts editor.
- Back in Excel, click on the "Automate" tab in the ribbon and choose "Run Script".
- Select the script you created (e.g., "cleanRange") and click "Run".
The script will now apply the CLEAN function to the specified range (G4:G2500), removing any non-printable characters from the cell values.
Note: Make sure to adjust the sheet name and range address in the code according to your specific worksheet.
- Thank you very much. However, when I attempt to run the script I get the following error:
"See line 3, column 9: Office Scripts cannot infer the data type of this variable. Please declare a type for the variable."
