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.

excel

Office Scripts

SergeiBaklan

Jun 03, 2023

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