Split multivalues in the same column in Office Script

Question

I have following table in Excel. I would like to split the values in the column Product only based on delimiters comma, semicolon or pipe. The code is working but that it splits just multi values in the cells and copies them in the same column with all values in other columns respectively. the issue is it create some blank rows based on the number of rows in the table. second issues is it split the value as well, so each characters goes to own cell, this is not expected. I would like to split the whole values only and not each character.

here is the table and desired output

and here is the Office Script

    async function main(workbook: ExcelScript.Workbook) {
        // Set the name of the sheet and the column to split
        let sheetName = "Sheet1";
        let columnNameToSplit = "Product";
        let delimiters = [",", ";", "|"];
    
        // Get the sheet and table
        let sheet = workbook.getWorksheet(sheetName);
        let table = sheet.getTables()[0];
    
        // Get the index of the column to split
        let columnIndexToSplit = table.getHeaderRowRange().getTexts()[0].indexOf(columnNameToSplit);
    
        // Get the data from the table
        let data = table.getRangeBetweenHeaderAndTotal().getValues();
    
        // Create an array to hold the new data
        let newData: (string | number)[][] = [];
    
        // Loop through each row of data
        for (let i = 0; i < data.length; i++) {
            let row = data[i];
            let cellValue = row[columnIndexToSplit];
    
            // Check if the cell value is a string and contains one of the delimiters
            if (typeof cellValue === "string" && delimiters.some(delimiter => cellValue.includes(delimiter))) {
                // Split the cell value by the delimiters
                let splitValues = cellValue.split(new RegExp(delimiters.join("|"), "g"));
    
                // Add a new row to the new data array for each split value
                for (let j = 0; j < splitValues.length; j++) {
                    let newRow = [...row];
                    newRow[columnIndexToSplit] = splitValues[j];
                    newData.push(newRow);
                }
            } else {
                // Add the original row to the new data array
                newData.push(row);
            }
        }
    
        // Clear the old data from the table and add the new data
        table.getRangeBetweenHeaderAndTotal().clear();
        table.addRows(-1, newData);
    }

unfortunately I get something like this

taller_ExcelHome · Accepted Answer · 2023-07-25T14:27:42.987

Removing extra spaces between keywords first, and consolidating all delimiters into a single character (ie. |) simplifies splitting.

async function main(workbook: ExcelScript.Workbook) {
  // Set the name of the sheet and the column to split
  let sheetName = "SplitDate";
  let columnNameToSplit = "Product";
  // let delimiters = [",", ";", "|"];
  // Get the sheet and table
  let sheet = workbook.getWorksheet(sheetName);
  let backupSheet = workbook.getWorksheet("Sheet2");
  // Restore(sheet, backupSheet);
  let table = sheet.getTables()[0];
  // Get the index of the column to split
  let columnIndexToSplit = table.getHeaderRowRange().getTexts()[0].indexOf(columnNameToSplit);
  // Get the data from the table
  let data = table.getRangeBetweenHeaderAndTotal().getValues();
  // Create an array to hold the new data
  let newData: (string | number)[][] = [];
  // Loop through each row of data
  for (let i = 0; i < data.length; i++) {
    let row = data[i];
    let cellValue = row[columnIndexToSplit];
    // Replace space and consolidate delimiters
    cellValue = cellValue.toString().replace(/\s+/g, '').replace(/,|;/g, '|')
    // Check if the cell value is a string
    if (typeof cellValue === "string") {
      // Split the cell value by the delimiter
      let splitValues = cellValue.split("|")
      // Add a new row to the new data array for each split value
      for (let j = 0; j < splitValues.length; j++) {
        let newRow = [...row];
        newRow[columnIndexToSplit] = splitValues[j].toString();
        newData.push(newRow);
      }
    } else {
      // Add the original row to the new data array
      newData.push(row);
    }
  }
  // Clear the old data from the table and add the new data
  table.getRangeBetweenHeaderAndTotal().delete(ExcelScript.DeleteShiftDirection.up);
  let selectedSheet = workbook.getActiveWorksheet();
  // Set number format for range E:E on selectedSheet
  table.getRange().getColumn(5).setNumberFormatLocal("@");
  table.addRows(-1, newData);
}

Split multivalues in the same column in Office Script

1 Answers1

Linked