I'm using Google Sheets to dynamically import election results from a RSS/XML file, but I'm having trouble separating information in the same cell.

Basically, the candidates and their results are all listed on separate lines in the same cell in a row that has the race title. I'm looking to break those candidates out, so they are on their own row, and the race title is copied for each row.

In one sheet, I use this formula to bring in the data: =IMPORTFEED("https://mielections.us/election/results/RSS/2020PRI_MI_CENR_SUMMARY.rss","","",250)

In another, I use these formulas to move over the data I want: Column A =IMPORTRANGE("https://docs.google.com/spreadsheets/d/1oEQMbB_mm55ssfz3Qf3fGQBpkpL2FUljQa0iqU6kEjs/edit#gid=0","Sheet1!A1:A250") Column B =IMPORTRANGE("https://docs.google.com/spreadsheets/d/1oEQMbB_mm55ssfz3Qf3fGQBpkpL2FUljQa0iqU6kEjs/edit#gid=0","Sheet1!D1:D250")

In a third column, I tried this, which separates the candidates in the cell to a new column: =SPLIT(B1,char(10)) However, I want each candidate to go to a new row and copy the race title.

I also tried this: =FLATTEN(Sheet2!A1:O250) However, it leaves a lot of blank rows and doesn't copy the race titles with each candidate.

Here's a copy of the spreadsheet:

Afterwards, you may use this script as the base of your script:

function extractData() {
  var ssData = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data");
  var lr = ssData.getLastRow();
  var ssOut = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Output");
  var count = 1;
  var out = []
  for (i = 2; i <= lr; i  ) {
    var data = ssData.getRange(i, 4, 1, 1).getValues().toString().trim().split("\n");
    var length = data.length;
    for (j = 0; j < length; j  ) {
      count  ;
      var populate = (ssData.getRange(i,1,lr,1).getValue());
      out.push([populate,data[j]]);
    }
  }
  ssOut.getRange(2,1,count-1,2).setValues(out);
}

This script uses a nested for loop to extract and rearrange the data from the Data tab to the Output tab. The script first counts the number of lines there are in the cells under the Summary column. Afterwards, the script splits the cells into multiple rows using the .split() function with the \n (new line) set as the delimiter. After that, the script will then duplicate the data under the title column based on the number of lines in the summary cells. After the data has been arranged, the script will then place the new data to the Output tab.

Output

After the script is done running, the Output tab should look like this: