Delete unnecessary space using office script in an excel report

This script removes leading and trailing spaces.

script excel script excel script excel

 

When I use the script ?

To delete spaces that are not necessary.

 

How to create the script ?

Read How to create, edit and select an Office Script in an excel report

 

How to create the button to associate it with the script ?

Read How to create a button and associated it to an Office Script in an excel report

 

How is/are the script(s) ?

Copy the code below and paste it into your script. You will see my comments in green if exist so follow the help to adapt to your need.

For 1 cell:


function main(workbook: ExcelScript.Workbook) {
    // change sheet1 by yours
    let sheet = workbook.getWorksheet("Sheet1");
    // change A2 by yours
    let rngval = sheet.getRange("A2").getValue() as string;
    const trimcell = rngval.trim();
    sheet.getRange("A2").setValue(trimcell);
}              
              

For multiple cells:


function main(workbook: ExcelScript.Workbook) {
    // change sheet1 by yours
    let sheet = workbook.getWorksheet("Sheet1");
    // change A2:A4 by yours
    let rng = sheet.getRange("A2:A4");
    let rngval = rng.getValues();
    for (let i = 0; i < rngval.length; i++) {
      rngval[i][0] = rngval[i][0].toString().trim();
    }
    rng.setValues(rngval);
}              
              

Interesting Topics