0

I want to capitalize the first letter of each word that gets entered into cells. I don't know anything about programming.

I found the following script which is close but this script CAPITALIZES EVERY WORD which isn't what I'm looking for:

function onEdit(e) {
if (typeof e.value != 'object') {
e.range.setValue(e.value.toUpperCase());
}
}
player0
  • 124,011
  • 12
  • 67
  • 124
John Moose
  • 1
  • 2

1 Answers1

0

In case you just need help with adapting the information, already provided in the previous answer, then here it is:

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

function titleCase(str) {
  return str.toString().split(/\b/).map(function(word) {
    return word ? word.charAt(0).toUpperCase() + word.slice(1).toLowerCase() : '';
  }).join('');
}

This will explicitly convert the text of any updated cell to Title Case, e.g. "a BIG brown bear" becomes "A Big Brown Bear".

If you wish to capitalize the initial letters only and leave the remainder of each word untouched, you may delete the .toLowerCase() part in the function above. Then the text "a BIG brown bear" will become "A BIG Brown Bear".

The titleCase function was taken from this answer.

EDIT: To skip capitalizing parts of words, conaining an apostrophe, you can replace the original titleCase function with this one, which introduces a specific work-around for in-word apostrophes:

function titleCase(str) {
  var isApostrophe = false;
  return str.toString().split(/\b/).map(function(word) {
    var result = word ? (!isApostrophe ? word.charAt(0).toUpperCase() : word.charAt(0).toLowerCase()) + word.slice(1).toLowerCase() : '';
    isApostrophe = (word == "'");
    return result;
  }).join('');
}

So, now the text "there's a 'BIG' brown bear" will be transformed into "There's A 'Big' Brown Bear".

EDIT 2: To apply this rule only to certain columns, you may replace the onEdit function with this version:

function onEdit(e) {
  if (typeof e.value != 'object') {
    if ([3, 5, 10, 11, 12, 13].indexOf(e.range.columnStart)<0) return;
    e.range.setValue(titleCase(e.value));
  }
}
Edvins
  • 406
  • 5
  • 9
  • Okay this code is great but the only problem I’m having is that it’s capitalizing letters after apostrophes – John Moose Nov 08 '17 at 13:49
  • Is there anyway that this this code can be used to target a specific range of cells and if so what would that look like? – John Moose Nov 10 '17 at 08:51
  • @JohnMoose: To limit the application of this, you may add a first line in *onEdit* function: `if (e.source.getActiveSheet().getName() !== "Sheet1" || e.range.rowStart < 2 || e.range.columnStart < 2) return` This will skip updating any sheet which is not Sheet1 and any cell that is before 2nd row and 2nd column. – Edvins Nov 10 '17 at 11:53
  • What if I need this code to apply to only columns 3, 5, 10, 11, 12, and 13? – John Moose Nov 11 '17 at 14:17