Useful Text Functions: UPPER, LOWER, and PROPER

There are a handful of text functions that I always seem to be using. These are all very simple but very helpful, and every time I show them to someone, I almost always get back something along the lines of “wow, cool!” So I thought I would share my most frequently used text functions in the next couple of posts.

Before I begin, I want to define for you what exactly a text function is. “Text” is basically anything in a cell. Text usually consists of letters, words, codes, names, or symbols, but can also include numbers.

A “function” is a formula that’s been pre-programmed into Excel. When you see “=” and an uppercase word followed by parentheses, that’s a function. For example, “=SUM(A1,B1)” is a function.

So therefore, a “text function” is a preset formula that let’s you do something to pieces of text. I use text functions pretty often to clean up my data. In today’s post, I’ll talk about the UPPER, LOWER, and PROPER functions.


The UPPER Function

The UPPER function simply capitalizes your letters.

For example, say you have the text “Savvy Spreadsheets” in cell B2, but you want to capitalize all of the letters. Instead of actually typing it out, you can use the UPPER function. Let’s return “SAVVY SPREADSHEETS” in cell C2. If you type “=UPPER(B2)” in cell C2, you are telling Excel to take whatever is in B2 and make it all uppercase.

upper function

This would be the result.

upper function result


The LOWER Function

The LOWER function is the opposite of the UPPER function. It makes all of your letters lowercase.

If you have “SAVVY SPREADSHEETS” in cell B2 and you want the lowercase version of it in C2, you would type “=LOWER(B2)” in C2.

lower function

And this is what it would look like.

lower function result


The PROPER Function

PROPER capitalizes just the first letter of each word.

Now say you have “savvy spreadsheets” in B2, and want to capitalize just the first “s” in each word. If you type in “=PROPER(B2)” in C2…

proper function

You have this result.

proper function result

Pretty straightforward right? UPPER, LOWER, and PROPER are really easy ways to make your data look uniform. And they are especially awesome when you have lots of data that’s inconsistently formatted. Imagine manually retyping thousands of names into proper case! No thank you, I’ll take a text function over that any day!

Next week, I’ll show you some more fun text functions 🙂

2 Responses to Useful Text Functions: UPPER, LOWER, and PROPER

  1. Jerry Cooper, CMA May 23, 2017 at 1:06 PM #

    Janet, thank you for your article on using the PROPER() function. Have you ever run into a situation where it does not work, as when three-letter acronyms (TLAs) are combined with “actual” words in a customer name? Is there any way to “teach” the PROPER() function how to deal with situations like that?

    Thanks,
    Jerry

    • Janet May 23, 2017 at 6:27 PM #

      Hi Jerry, great question! I’ve definitely had tricky situations where the PROPER function wouldn’t quite work by itself. In these cases, I usually need to use multiple text functions within a formula in order to get the right result. In your example with the TLAs, if there is a comma before the acronym, you could use something like this (where A1 has the name):

      PROPER(LEFT(A1,FIND(“,”,A1)-1))&”, “&UPPER(RIGHT(A1,3))

Leave a Reply