2

I would like to convert all text in a range of cells to Title Case automatically.

How can I do that?

  • 1
    Is there a difference in title-case and proper-case? There is the formula =PROPER("string") – Xzila Apr 4 '17 at 5:46
1

Here is an Apps Script for this purpose. The function titleCaseRange title-cases the content of the currently selected range (which may be a single cell or a rectangular block). The function titleCase is used by it to handle an individual string.

function titleCaseRange() {
  var range = SpreadsheetApp.getActiveRange();
  var values = range.getValues();
  if (values.map) {
    range.setValues(values.map(function(row) {
      return row.map(titleCase);
    }));
  }    
  else {
    range.setValue(titleCase(values));
  }
}

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

The logic can be adapted to other situations, e.g., you may want to have a custom function like =title(A3:B8) that converts a range to title case (but no longer in the same place as the data originally was). This would use the same titleCase above, but the rest would be different:

function title(values) {
  if (values.map) {
    return values.map(function(row) {
      return row.map(titleCase);
    });
  }    
  else {
    return titleCase(values);
  }
}
  • Thank you so much. What about, if i want to select automatically 3 cells every 7 rows ? – Marvin JERNIDIER Dec 3 '16 at 13:07
  • Then you should probably replace the values.map things by loops like for (var i = 0; i <values.length, i++) { for (var j = 0; j < values[0].length; j++ ) { // some logic to decide if you want title case or not } } – user135384 Dec 3 '16 at 15:12

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.