12

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.

| improve this answer | |
  • 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.

| improve this answer | |

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