CODEWITHSUNDEEP
google-apps-scriptcapitalize
SATH59
SATH59

Reputation: 159

Apps script how to format a cell to Proper Text (Case)

I have a form coming to my sheet, and sometimes my customers enter the text in lower or upper case, and aso with line breaks

I have this code to make a cell UPPERCASE and remove the line breaks

var val = responses.getRange( i + 1 , 13).getValue();
    val = val.replace(/\n/g,", ");
    responses.getRange( i + 1 , 13).setValue(val.toUpperCase());

What I need is to make that cell to Proper Case, not UpperCase

For example if the text is "hello how ARE YOU" I want to convert to "Hello How Are You" or also to "Hello how are you"

Upvotes: 2

Views: 4269

Answers (4)

Chris Drane
Chris Drane

Reputation: 11

This is my custom function I use to specifically convert surnames to proper case. It handles issues with surname such a O'Brien, Rees-Mogg and McGregor.

function properCase(str) {
  
  if (typeof str != "string")
    throw `Expected string but got a ${typeof str} value.`;

  // remove possibility of trailing spaces and convert to lowercase
  str = str.trim().toLowerCase();

  ([" ", "'", "-", "Mc"]).forEach(function (char) {
    str = str.split(char).map(function (word) {
      return word.charAt(0).toUpperCase().concat(word.slice(1));
    }).join(char);
  });

  return str;

}

Upvotes: 1

Ze Za
Ze Za

Reputation: 21

Use the following code instead. It will capitalize each word inside the cell. You can set a trigger to "on edit" or to run once a day or every hour, etc.

With this code, you can easily change the range, so instead of C3:D (start at row 3 and include columns C AND D) you can use C:D (all rows in columns C and D), or C:C, or C3:C, etc. You get the idea. I tested the code and I'm currently using it. No glitches.

function textCase1Name() {

  const range = SpreadsheetApp.getActiveSheet().getRange("C3:D")

  const values = range.getDisplayValues()
  .map(row => row.map(col => (col) ? col.toLowerCase().replace(/\b[a-z]/ig, function(match) {return match.toUpperCase()}) : col))

  range.setValues(values);
}

Upvotes: 0

user558720
user558720

Reputation: 174

Here is what I did in order to use the PROPER function in apps script for anyone else.

First I put the function in the cell using the name (in this case customer). Then I used the getDisplayValue to get the actual text. Then wrote it back out to the cell. 

    customer = sht.getRange(row, col).setValue('=PROPER("'+customer+'")').getDisplayValue(); 
    sht.getRange(row, col).setValue(customer);

A bit of a hack/alternative but does the job.

Upvotes: 0

Tanaike
Tanaike

Reputation: 201643

  • You want to modify from hello how ARE YOU to Hello How Are You and Hello how are you using Google Apps Script.

If my understanding is correct, how about this modification? Please think of this as just one of several answers.

The flow of this modification is as follows.

At first, the value is modified to lowercase.

  • At pattern 1, the first characters of each word modify to uppercase.
  • At pattern 2, the first character of value modifies uppercase.

Sample script:

var val = "hello how ARE YOU";

var pattern1 = val.toLowerCase().replace(/\b[a-z]/ig, function(match) {return match.toUpperCase()});
var pattern2 = val.toLowerCase().replace(/^[a-z]/i, function(match) {return match.toUpperCase()});

console.log(pattern1) // Hello How Are You
console.log(pattern2) // Hello how are you

Reference:

If you need other patterns, can you provide them?

Upvotes: 4

Related Questions