itil

ITIL Reporting

excel

ITIL 4

excel

Power BI

excel

Excel Formula | Macro | Script

Extract words from a cell using office script in an excel report

This script will extract all words from a cell into 1 single cell for each word.

script excel script excel

 

When I use the script ?

When I need to split the cell into multiple cells (one cell for one word).

 

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 by yours
    let cellvalue = sheet.getRange("A2").getValue() as string;
    // if words are separated by a comma space for instance, change (" ") by (", ")
    let sepword = cellvalue.split(" ")
    // change 2 by yours
    let startrow = 2
    // change column A by yours
    sepword.forEach((word, index) => { sheet.getRange(`A${startrow + index}`).setValue(word) });
}

Interesting Topics