How do I split multiline cells into arrays for Google Sheets?

Question

I keep a spreadsheet of the titles and issue numbers I've read for various comics. I want to have a column that provides the count of the issues read for each title. Some titles have issue numbers in multiple lines. For example, the run of Avengers starting in 2016 has the following issue numbers listed in one cell, each range on a new line within the cell:

#1-11
#1.1-5.1
#1MU
#672-676

I tried to write a script that would separate each line into an array item, and then for each item in the array, extract the numbers using regular expressions to perform calculations to determine the total count. (The count of the above issues is 22, but the problem is getting the script to determine that for me and for the other titles as I update issue numbers.)

Here's what I have so far:

function calcIssueCount(x) {
  // Set initial value to 0:
  var issueCount = 0;
  // Make an array from the lines of the cell
  // split by the line break:
  var box = x.split("\n");
  for (var line in box) {
    // Determine if the line includes a 
    // range of issue numbers, indicated
    // by presence of a hyphen:
    if ("-" in line === True) {
      // Remove the # symbol from the string:
      line = line.replace("#","");
      // Extract the number before the hyphen
      // and delete the hyphen:
      var a = line(/[0-9}+\.|[0-9]+-/);
      a = a.replace("-","");
      // Extract the number after the hyphen 
      // and delete the hyphen:
      var b = line(/-[0-9}+\.|[0-9]+/);
      b = b.replace("-","");
      // Turn a and b into numbers:
      a = number(a)
      b = number(b)
      // Subtract the range start from the 
      // range end:
      var c = b - a;
      // Add 1 because a range of 1-5 includes 5
      // issues, while 5-1 is only 4:
      c += 1;
      // Update the count for the number of 
      // issues in the cell by adding the 
      // line's value:
      issueCount += c;
    }
    else {
      // If there's no hyphen, there's no 
      // range; the count of issues on the
      // line is 1:
      issueCount += 1;
    }
}
}

calcIssueCount(x) would have x as the cell name (e.g., D15).

I'm likely making mistakes all over the place, but I especially think I'm not understanding something about getting the cell data into the array into the first place. What am I missing here?

Answer 1

You want to retrieve "22" from the following value in a cell.

#1-11
#1.1-5.1
#1MU
#672-676

As the logic for retrieving "22" from the value, I understood as follows.

• Retrieve 1 and 11 from #1-11.
• Retrieve 1.1 and 5.1 from #1.1-5.1.
• Retrieve 672 and 676 from #672-676.
• Subtract before number from after number for each line.
• Add 1 to all lines.
• For the line without -, add only 1.

From above logic, (11 - 1 + 1) + (5.1 - 1.1 + 1) + (1) + (676 - 672 + 1) = 22 can be obtained. If my understanding of your logic is correct, how about the modification like below?

Modification points :

• When for (var line in box) { is used, each element can be retrieved by box[line].
  • In this modicication, I used forEach.
• In Javascript, the boolean express "true" and "false".
• Line is not an object. So "-" in line occurs an error.
  • In this modification, I used indexOf().
• In Javascript, number() is Number().
• When you want to use calcIssueCount(x) as a custom function, in your current script, no values are returned. So if you want to retrieve issueCount, please add return issueCount.
• I couldn't understand about the function of line(/[0-9}+\.|[0-9]+-/). I'm sorry for this.

In my modification, I retrieved both the before and after numbers using a regex of ([0-9.]+)-([0-9.]+). I think that there are several solutions for your situation. So please think of this as one of them.

Modified script :

In this pattern, your script was modified.

function calcIssueCount(x) {
  // Set initial value to 0:
  var issueCount = 0;
  // Make an array from the lines of the cell
  // split by the line break:
  var box = x.split("\n");
  box.forEach(function(e){
    if (e.indexOf("-") > -1) {
      var numbers = e.match(/([0-9.]+)-([0-9.]+)/);
      // Extract the number before the hyphen
      // and delete the hyphen:
      var a = Number(numbers[1]);
      // Extract the number after the hyphen 
      // and delete the hyphen:
      var b = Number(numbers[2]);
      // Subtract the range start from the 
      // range end:
      var c = b - a;
      // Add 1 because a range of 1-5 includes 5
      // issues, while 5-1 is only 4:
      c += 1;
      // Update the count for the number of 
      // issues in the cell by adding the 
      // line's value:
      issueCount += c;
    } else {
      // If there's no hyphen, there's no 
      // range; the count of issues on the
      // line is 1:
      issueCount += 1;
    }
  });
  return issueCount;
}

This is other sample script. In this pattern, the regex is not used. The result is the same with pattern 1.

function calcIssueCount(x) {
  return x.split("\n").reduce(function(c, e) {
    if (e.indexOf("-") > -1) {
      var numbers = e.slice(1).split("-");
      c += (Number(numbers[1]) - Number(numbers[0])) + 1;
    } else {
      c += 1;
    }
    return c;
  }, 0);
}

Result :

Note :

• In this modified script, the regex of ([0-9.]+)-([0-9.]+) is used for the sample value. If you want to use values with other patterns, please tell me. At that time, can you show me more samples?

References :

• Custom Functions in Google Sheets
• Array.prototype.forEach()
• String.prototype.indexOf()
• Number
• String.prototype.match()

If I misunderstand your question, I'm sorry.