How to detect if a cell value start with an apostrophe in new Google Sheets

0
votes

Assume that in a Google Sheets spreadsheet there is a fill in the blank question in cell A2:

I ____ happy.

The answer should be written in cell B2. The correct answers are

  • 'm
  • am

Examples of wrong answers:

  • m
  • a
  • is
  • are
  • be

The test formula in C2 is =REGEXMATCH(B2;"^['a]m$").

The problem is that when B2 value is 'm the above formula returns FALSE instead of TRUE.

By the other hand, by using a Google Apps Script function to check the cell value, getValue() returns m instead of 'm.

function check(){
  var file = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = file.getSheetByName('Hoja 1');
  var range = sheet.getRange('B2');
  var value = range.getValue();
  throw value;  
}

UPDATE:
https://stackoverflow.com/a/15992619/1595451 and https://stackoverflow.com/a/16698417/1595451 are answers for questions relaed to the opposite problem, removing the leading apostrophes. They use substring(1) to remove the apostrophes. At this time, when the value is 'm it return an empty string ("").

Attribution: The original question was posted in the Google Drive Help forum in Spanish by Salvador Pérez Gómez. Reconocer expresiones que comienzan por apóstrofe o comilla simple ( ' ).
1
google-apps-scriptgoogle-sheets

1 Answers

2
votes

Excel and Google Sheets look for a leading apostrophe to define for formatting, so it's ignored. So, using your REGEXMATCH formula, it evaluates to false because it skips the apostrophe.

Have the users use two single apostrophes in their response - one to be read as formatting, the other to be read as an apostrophe in English. The regex will return true.