Google-sheets – Use countblank to count number of blank merged cells in Google Sheets

Use a custom function.

Code

function result(range) {
  var splitCol = 1; // split on column B
  var output2 = [];
  for(var i=0, iLen=range.length; i<iLen; i++) {
    var s = range[i][splitCol].split("\n");    
    for(var j=0, jLen=s.length; j<jLen; j++) {
      var output1 = []; 
      for(var k=0, kLen=range[0].length; k<kLen; k++) {
        if(k == splitCol) {
          output1.push(s[j]);
        } else {
          output1.push(range[i][k]);
        }
      }
      output2.push(output1);
    }    
  }
  return output2;
}

Explanation

The script evaluates each row, and particularly the second column of each row (in JavaScript arrays are zero based, so column 2 corresponds to index 1 of the array). It splits the contents of that cell, into multiple values and uses the "\n" as delimiter (line feed). After that it adds the existing info to an array and only add the individual results, when it hits index 1 (k == 1). The newly prepared row is then added to another array, that's being returned to show the result.

Screenshot

data

result

Example

I've created an example file for you: multi-line cells into new rows.

• Add the script under Tools > Script editor.
• Save the script, the name doesn't matter.
• Use the custom function in a cell just like a built in (see the screenshot above).

I don't think rowHeight can help you here, because it refers to the entire row, not to any particular cell in it. Sorting each column by RowHeight would just rearrange the rows without making your spreadsheet more compact.

Here is the script that sorts each column by the length of cell content, ascending. The first row (headers) is left in place; empty cells are ignored.

function myFunction() {
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var sheet = ss.getActiveSheet();
   var range = sheet.getDataRange();
   var width = range.getWidth();
   var height = range.getHeight();
   var column, i, j, content;
   for (j = 1; j <= width; j++) {
     column = [];
     for (i = 2; i <= height; i++) {
       content = range.getCell(i,j).getValue();
       if (content) {
         column.push(content);
       }
     }
     column.sort(function (a,b) {return a.length - b.length;});
     for (i = 0; i < column.length; i++) {
       range.getCell(i+2,j).setValue(column[i]);
     }
     for (i = column.length; i < height-1; i++) {
       range.getCell(i+2,j).setValue("");
     }
  }
}

But in practice, this particular sort does not help all that much, because (as you noticed) the longer texts can still match up against shorter texts. Here is another sort, somewhat along the lines of what you mentioned: after sorting, each column is dropped down so that all the longest entries are in the same row. This will typically result in some empty cells at the top.

function myFunction2() {
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var sheet = ss.getActiveSheet();
   var range = sheet.getDataRange();
   var width = range.getWidth();
   var height = range.getHeight();
   var column, i, j, content;
   for (j = 1; j <= width; j++) {
     column = [];
     for (i = 2; i <= height; i++) {
       content = range.getCell(i,j).getValue();
       if (content) {
         column.push(content);
       }
     }
     column.sort(function (a,b) {return a.length - b.length;});
     for (i = 0; i < column.length; i++) {
       range.getCell(height-column.length+i+1,j).setValue(column[i]);
     }
     for (i = 2; i <= height-column.length; i++) {
       range.getCell(i,j).setValue("");
     }
  }
}