Google Sheets Functions – PROPER, UPPER, LOWER, TRIM

In this post, we’re going to look at how we change the format of text to suit our needs, using the functions PROPER, UPPER, LOWER, and TRIM.

It’s particularly useful when working with text that has come from, for example, a form, a different computer system, or indeed someone has typed in on your Sheet. This is because the capitalisation isn’t the way we want it and the text may contain unwanted spaces, which can cause problems on your Sheet. We’ll cover the following areas:

• Using the PROPER function to capitalize each word
• Using the UPPER function to capitalize all letters
• Using the LOWER function to put words into lowercase
• Using the PROPER and TRIM functions to clean up text
• Using ARRAYFORMULA to copy PROPER function to all rows
• Capitalizing only the first letter of a sentence and putting the rest in lowercase
• Changing a name to initials

---

Example 1 – Using the PROPER function to capitalize each word

The PROPER function capitalizes every word in the text, which is useful for correcting the format of names. Here we’ve got my name in various formats.

As we can see the syntax of the PROPER function and indeed the UPPER, LOWER and TRIM functions is very simple, just add the text or cell reference in brackets.

So I write the following in cell A2.

=proper(A1)

Then I copy that down in cells B2 and B3, and as we can see it’s corrected the format of the name.

---

Example 2 – Using the UPPER function to capitalize all letters

Here we have a part number which should be in UPPERCASE.

Similar to PROPER you just put the text or cell reference in brackets.

=upper(A1)

And it changes all the letters to uppercase.

---

Example 3 – Using the LOWER function to put words into lowercase

Here I want to change my messy name to lowercase.

I just write the following function:

=lower(A1)

---

Example 4 – Using the PROPER and TRIM functions to clean up text

One problem with receiving text input from forms, computer systems, etc is that text input can be in different formats and also can have unwanted spaces, which can mess up your formulas and how you use the data.

Here is an example where my name has been filled out in a form, but the users have entered it in different ways.

We can use the PROPER function to tidy up the format of the text, as we saw above, and we can use the TRIM function to get rid of those unwanted spaces, whether they are before the text, in the middle of the text, or afterwards.

So, we wrap the TRIM function up in the PROPER one:

=proper(trim(A1))

As you can see, it tidies it up well, making it easy to use the data afterwards, or even just to make it look better.

---

Example 5 – Using ARRAYFORMULA to copy PROPER function to all rows

In the example 1, I copied the PROPER function down into each row. There’s a quicker and better way. Here I have some students’ names and I want to tidy up the format.

In cell B1, I write the following formula:

=arrayformula(proper(A1:A))

This looks at everything in column A and places the corrected format in column B, without having to copy down the formula into rows 2 and below.

Note, that when using the PROPER function, names which contain a capital letter in the middle of the word, such as, McCarthy, aren’t displayed correctly, in that the second c is also in lowercase, e.g. Mccarthy.

---

Example 6 – Capitalizing only the first letter of a sentence and putting the rest in lowercase

The PROPER function works well with names, but what about normal sentences? We usually don’t want every word to start with a capital letter.

Here I have a sentence all in uppercase and as it seems that the person is ‘shouting’, I want to correct it automatically and add a capital letter at the start.

This sounds like a simple thing to do, but the formula involved is quite long. However, the way it works is quite simple.

=UPPER(LEFT(A1,1))&LOWER(RIGHT(A1,LEN(A1)–1))

Let’s go through it from left to right. Firstly, it puts the first character to the left in uppercase, i.e. the first letter of the sentence. Then it puts the rest of the sentence in lowercase, by finding out the length of the text and ignoring the 1 character at the start of the sentence.

Here’s the result:

To adapt it to your needs, just edit the cell reference A1.

---

Bonus Example – Changing a name to initials

In Spain, names can be quite long as people can have two first names and also two surnames. A common practice in business, is to refer to people in emails and documents by their initials. We can get sheets to display the initials by looking at the full name and returned the first letter of each name.

This example is not strictly to do with the functions in this post, but is an example of how other functions can be used to manipulate names. The formula is particularly long, but in fact, from the second MID function, it just the same formula repeated to analyse each word in sequence.

=LEFT(A1)&MID(A1,FIND(“#”,SUBSTITUTE(A1&” “,” “,“#”,1))+1,1)&MID(A1,FIND(“#”,SUBSTITUTE(A1&” “,” “,“#”,2))+1,1)&MID(A1,FIND(“#”,SUBSTITUTE(A1&” “,” “,“#”,3))+1,1)

As we can see it takes the four word name and returns the person’s initials.

If the initial name was in lower case, the initials would also be in lowercase.

With the formula split into 4 component parts, you can see that after the initial LEFT function, there are 3 parts that are almost identical. The only difference is that in the SUBSTITUTE formula, the number changes from 1 to 3.

=LEFT(A1)

&MID(A1,FIND(“#”,SUBSTITUTE(A1&” “,” “,”#”,1))+1,1)

&MID(A1,FIND(“#”,SUBSTITUTE(A1&” “,” “,”#”,2))+1,1)

&MID(A1,FIND(“#”,SUBSTITUTE(A1&” “,” “,”#”,3))+1,1)

To adapt it to your needs, just edit the cell reference A1. If you only have 3 names, you can omit the last part. If you have more than 4 names, just add another &MID part, and make sure the number on the SUBSTITUTE brackets goes up by 1 (for example to 4).

---