Forum Discussion
Bkkendig
Jun 03, 2023Copper Contributor
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.
- Dec 18, 2020You said when you open the extension it looks like the chrome one, and then you say you can't install this extension? confusing..
SergeiBaklan
Jun 03, 2023MVP
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()
Bkkendig
Jun 03, 2023Copper Contributor
Thank you. Your solution works!!
- SergeiBaklanJun 04, 2023MVP
Bkkendig , you are welcome