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.

These two simple solutions certainly helped me with what I needed to achieve so I hope it will help you, too ????

Please check out other posts in my blog. Thanks.