Dream Big Reach Far
Upper Case in Google Sheets Using Formula or JavaScript

Upper Case in Google Sheets Using Formulas and JavaScript

Just recently I ran into the need of applying upper case in Google Sheets – I needed to capitalise the content that was initially written in sentence case. I spent some time looking for the solution and stopped at something that worked great for me.

Click here to scroll down to the JavaScript solution or continue reading for the formulas.

The formulas are also helpful if you need to change your text to lower case or sentence case. So, if you are in that place that I was an hour ago, then let’s go ‘back to the future’ with me.

Here is the formula for modifying text to upper case in Google Sheets:

=UPPER(A1)

…where =UPPER() is the actual formula that does the trick and A1 is a cell with the text that you want to capitalise. Just type it out in an empty cell and you will see it copy your A1 content and change it to upper case. Done.

To change the text to lower case, try this:

=LOWER(A1)

…where =LOWER() is the formula to change your text to lower case and A1 is the cell you need to change.

And if you need to bring everything back to sentence case, try this:

=PROPER(A1)

…where =PROPER() is the formula and A1 is your target cell.

Easy, right?

The only downside of the above formulas is that you have to use them in new cells which leaves you with duplicates and the need to replace the old cells with the new ones.

Hence, here is another solution (and I do like it better).

JavaScript Solution

Try using JavaScript in the Code Editor of the Google Sheets file to automatically capitalise everything on edit. Here is how you can implement it.

In your Google Sheets, go to “Tools”, then “Script editor…” and paste the following code:

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

That’s it! You don’t need to set any triggers. From now on, everything you change will be automatically capitalised.

LoveinLions

Content creators, explorers and DestinAsian competition winners - in love with everything digital.

About the Author:
Content creators, explorers and DestinAsian competition winners - in love with everything digital.


2 Comments:

  1. Christopher
    December 17, 2017
    Reply

    Very easy to understand and follow! Thanks.

    • Vladi
      December 17, 2017
      Reply

      Glad this post was helpful. Thank you Christopher!


Leave a Comment!

Your email address will not be published. Required fields are marked *