Search words using office script in an excel report

There are 2 scripts, one is to find one single word and the other is to look for multiple words no matter if they are lower or upper case letters (case-insensitive).

script excel script excel script excel script excel

 

When I use the script ?

When I have to search a particular word to do some specific actions.

 

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.

To find 1 word:


function main(workbook: ExcelScript.Workbook) {
    // change sheet1 by yours
    let sheet = workbook.getWorksheet("Sheet1");
    // change A2:A3 by yours
    // putting only A2 or A3 will not work, need to have minimum 2 cells
    // to search the full sheet, change getRange("A2:A3") by getUsedRange()
    let myvalue = sheet.getRange("A2:A3");
    // change inc001 by yours
    // for a total match, change completeMatch: false by completeMatch: true
    // for case-sensitive, change matchCase: false by matchCase: true
    let findval = myvalue.find("inc001", { completeMatch: false, matchCase: false })
    if (findval !== undefined) {
        // if the word is found, it will select the cell, if not, do nothing
        findval.select();
    }
}              
              

To find multiple words, there are 2 options: “option 1” no need to put the results in the sheet and “option 2” will display the result in the sheet. Option 1:


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");
    range.getValues().forEach((row, index) => {
        // for case-sensitive, remove .toLowerCase()
        let cellValue = row[0].toString().toLowerCase();
        // change inc001 and inc002 by yours
        // if more word to find add another -> || cellValue.includes("inc003")
        // “or” is || and “and” is &&
        // for a total match, change cellValue.includes by cellValue === and remove the () between the word
        if (cellValue.includes("inc001") || cellValue.includes("inc002")) {
            // change onsole.log by your code
            console.log("found");
        } else {
            console.log("not found");
        }
    });
}              
              

Option 2:


function main(workbook: ExcelScript.Workbook) {
  // change sheet1 by yours
  let sheet = workbook.getWorksheet("sheet1");
  // change A2:A4 by yours
  let row_count = sheet.getRange("A2:A4").getRowCount();
    for (let i = 1; i <= row_count; i++) {
        // change 0 if not column A, for instance column B = 1, column C = 2, etc.
        // for case-sensitive, remove .toLowerCase()
        let cellValue1 = sheet.getCell(i, 0).getText().toLowerCase();
        // change inc001 and inc002 by yours
        // if more word to find add another -> || cellValue.includes("inc003")
        // “or” is || and “and” is &&
        // for a total match, change cellValue.includes by cellValue === and remove the () between the word
        if (cellValue1.includes("inc001") || cellValue1.includes("inc002")) {
          // change 1 if not column B to put results
          sheet.getCell(i, 1).setValue("found");
        } else {
          sheet.getCell(i, 1).setValue("not found");
        }
    }
}              
              

Interesting Topics