Google-sheets – Convert all text to to Title Case in Google Spreadsheets

google sheets

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

How can I do that?

Best Answer

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);
  }
}
Related Topic