Clean function as script

[Bkkendig]

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.

[#2]

@Bkkendig 

To use the CLEAN function within an Office Script in Excel, you can follow these steps:

1. Open your Excel workbook.
2. Click on the "Automate" tab in the ribbon.
3. Click on "Script Lab" to open the Office Scripts editor.
4. In the Office Scripts editor, create a new script or open an existing one.
5. 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();
    });
}

1. Save the script and close the Office Scripts editor.
2. Back in Excel, click on the "Automate" tab in the ribbon and choose "Run Script".
3. 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.

[#3]

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."

[#4 · Accepted solution]

@Bkkendig 

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()

[#5]

Thank you. Your solution works!!

[#6]

@Bkkendig , you are welcome