11

I have a spreadsheet I'm sharing with others to collect some data. Each person is just (supposed to be) putting a single character in any of a range of cells.

How can I cause those cells to convert to uppercase?

Using =UPPER() in each cell would seem to be a non-starter, because it'll be overwritten by whatever they enter.

  • Could someone expand on this answer please? Specifically, when I have written and saved my script, how should I execute it on a given range of cells back in my Google Sheet? – tomh Aug 13 '18 at 11:30
12

Here is a script that automatically converts input to upper case:

function onEdit(e) {
  if (typeof e.value != 'object') {
    e.range.setValue(e.value.toUpperCase());
  }
}

From the spreadsheet, go Tools > Script Editor. Put the above code in the code window (replacing anything pre-filled there), and save.


If you wanted to enforce upper case and single-character entries, then data validation could be used, with custom formula

=regexmatch(A1,"^[A-Z]$")

This is assuming the upper left corner of the range is A1, and that you expect letters of Latin alphabet. The regular expression ^[A-Z]$ means: beginning of string; single character in the range A-Z; end of string.

  • Could someone expand on this answer please? Specifically, when I have written and saved my script, how should I execute it on a given range of cells back in my Google Sheet? – tomh Aug 13 '18 at 11:30
  • @tomh this script work for a whole spreadsheet. did you consider to use =ARRAYFORMULA(UPPER(A:Z) ? – user0 Aug 13 '18 at 14:16
  • Hi - I'm asking for the step by step process to take the script and apply it to a range of highlighted cells in a sheet. Ideally from a menu command – tomh Aug 13 '18 at 15:02
  • @tomh did you get this script workin' ? – user0 Aug 13 '18 at 17:04
  • No. I used an add-on called Cases to achieve the same effect. But I'd like to know how to apply a script to a series of cells in a sheet, and I think it would be a useful answer on this post. – tomh Aug 13 '18 at 18:45
1

This is What you want.

Just change this line in the script...

ui.createMenu('Custom Menu')

...to this...

ui.createMenu('Case Change')

... for your own sake.

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