6

Is there a function that checks if the strikethrough format has been applied to the cell contents? I would like use a formula similar to (=IF(HAS_STRIKETHROUGH(C5), 1,0)

7

This appears to require a custom function, because spreadsheet formulas do not have access to cell formatting. I wrote a function that returns True when the reference cell has strikethrough, and False otherwise. For example, you can enter =has_strikethrough(B2:C10) and get an array of True/False values. (This was of using custom functions is much more efficient than applying them to each cell individually.)

The script should be placed in the Script Editor, found in the Tools menu. See this page to get started with scripts.

/** 
 * Returns True if the referenced cell has strikethough, False otherwise
 * Supports ranges
 * @param {A1}  reference Cell reference
 * @customfunction
 */
function has_strikethrough(reference) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var formula = SpreadsheetApp.getActiveRange().getFormula();
  var args = formula.match(/=\w+\((.*)\)/i);
  try {
    var range = sheet.getRange(args[1]);
  }
  catch(e) {
    throw new Error(args[1] + ' is not a valid range');
  }
  var lines = range.getFontLines();
  var output = [];
  for (var i = 0; i < lines.length; i++) {
    output.push(lines[i].map(function(a) {return a == 'line-through';}));
  }
  return output;
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.