Count characters and words using an office script in an excel report

This script will count the number of characters and/or the number of words.

script excel script excel

 

When I use the script ?

It happens that I need to know those numbers in order to perform another action.

 

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.


function main(workbook: ExcelScript.Workbook) {
    // change sheet1 by yours
    let sheet = workbook.getWorksheet("Sheet1");
    // change A2:A4 by yours
    let range = sheet.getRange("A2:A4"); 
    let values = range.getValues();
    for (let i = 0; i < values.length; i++) {
        let text = values[i][0].toString();
        let charcount = text.length; 
        let wordcount = text.split(/\s+/).filter(word => word.length > 0).length; 
        // change column B by yours
        sheet.getRange(`B${i + 2}`).setValue(charcount); 
        // change column C by yours
        sheet.getRange(`C${i + 2}`).setValue(wordcount); 
    }
}              
              

Interesting Topics