Google sheets year range sequence formula

Question

I am in need of a formula for google sheets which can list out a range of years vertically based on the data in a specific cell. If the input is not a range then the output should just be the single year.

Example input: 1977-1979 1978-1979 1980

Need an array formula output which provides these results vertically in the same column: 1977 1978 1979 1978 1979 1980

Thank you for any help! I've been trying to figure this out for hours. If it's not possible in google sheets, I have excel.

Answer 1

• open the spreadsheet
• under the Extensions option, select Apps Script
• it would open an new window, clear all the default code
• and paste the following code there

function rangeToYear(input) {
  input=input.toString().trim().split(" ");
  var data=[];
  for(var i=0;i<input.length;i++){
    var tempdata = input[i].trim().split("-");
    if(tempdata.length==1){
      data.push(tempdata);
      continue
    }
    for(var j=parseInt(tempdata[0]);j<=parseInt(tempdata[1]);j++){
      data.push(j);
    }
  }
  return data;
}

• to run this code, open the spreadsheet
• select the required cell, and type =rangeToYear(cell_number)
• replace cell_number with your cell number, example ( B1 )

the above code splits the string into an array, based upon the spaces, later for every array element, again split the array based upon the hyphen, if not present return the array element, else iterate from the startYear to endYear adding all the intermediate years

the above code works well with the following types of input data

1. 1977-1979
2. 1978-1979
3. 1980
4. 1977-1979 1978-1979 1980

OUTPUT FOR INPUT TYPE-1

OUTPUT FOR INPUT TYPE-2

OUTPUT FOR INPUT TYPE-3

OUTPUT FOR INPUT TYPE-4

additionally if you want to vertically put the output in the same cell instead of the same column

change return data; to return data.join("\n");

i hope this is what you were looking for, and i hope this code helped you out, any problems or if you need solution to any other use cases, let me know :)