How To Change Case In Excel:
Capitalize First (Or ALL) Letters + More!

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

There are times when letter case in Excel needs to be changed or corrected.

Doing the changes by hand is extremely boring. Not only that, it’s also inefficient.

In some cases, it’s also close to impossible…

…Imagine changing letter case in large data sets or long data strings!

Well, I’m here to share the good news: You don’t have to do it manually at all! Excel happens to offer a few functions that allow us to address letter case in a much more effective manner.

Kasper Langmann, Co-founder of Spreadsheeto

There are two functions that are most notable: ‘UPPER’ and ‘LOWER’. They do exactly what their names imply.

There is also a function that capitalizes the first letter of proper names and places. It is called, ‘PROPER’.

All three have their uses and make life a lot more convenient for us Excel users.

There might be some (rare) cases, where these functions aren’t going to provide a solution. Don’t worry, I’ll show you exactly what to do in those cases.

Kasper Langmann, Co-founder of Spreadsheeto

For now, let’s set the foundation by learning how to use the ‘UPPER’, ‘LOWER’, and ‘PROPER’ functions on their own.

*This tutorial is for Excel 2019 for Windows. Got a different version? No problem, you can still follow the exact same steps.

Get your FREE exercise file

Before you start:

Throughout this guide, you need a data set to practice.

I’ve included one for you (for free).

Download it right below!

Download the FREE Exercise File

Download exercise file
Download free exercise file

Capitalize the first letter using
the function ‘PROPER’

The ‘PROPER’ function works the same way its cousins ‘LOWER’ and ‘UPPER’ do.

The difference is, that it only capitalizes the first letter of each substring of text. This could be a single word. It could also be multiple words such as first and last names, cities and states, abbreviations, suffixes, and honorifics/titles.

Kasper Langmann, Co-founder of Spreadsheeto

The syntax for all three of the functions that change case is the same.

It’s simply the function and the text (or cell reference) within parenthesis.

Here’s the syntax for ’PROPER’:

‘=PROPER(text)’

Now let’s look at some examples of text strings and the results of applying the ‘PROPER’ function to them.

Example of PROPER function

Notice that the function takes string elements whether they are capitalized or not. Then it converts the entire string to proper form.  This is quite straightforward.

But there are a couple of issues to be aware of when using the ‘PROPER’ function…

The main issue is that it does not know the difference between an actual word and an abbreviation – like an acronym for instance. For example, if we apply the ‘PROPER’ function to something like ‘FIFA’, it will return ‘Fifa’. This is not the desired outcome and should be kept in mind.

Kasper Langmann, Co-founder of Spreadsheeto

Another example of this would be using the suffix ‘md’ for a medical doctor. Again, ‘PROPER’ will see this more as a word and will return ‘Md’.

If you want both letters capitalized, place a period after each letter in the abbreviation.

Or apply the ‘UPPER’ function to just that part of the original string.

Capitalizing all letters using
the function UPPER

Now that we know how to capitalize the first letter of a text string, let’s find out how to make all caps.

This requires the ‘UPPER’ function and as we already noted, its syntax is much like that of ‘PROPER’.

‘=UPPER(text)’

Consider a case in where we have a worksheet that contains names of customers. This gets exported out of a database in all lowercase form. No problem, we just bring the ‘UPPER’ function into the game.

Kasper Langmann, Co-founder of Spreadsheeto

Apply ‘UPPER’ to our cell references containing the customer names to make all caps.

UPPER function example

Changing uppercase to lowercase
using the function ‘LOWER’

If you have a list that comes as all caps, you can use the ‘LOWER’ function to convert to all lower case.

‘=LOWER(text)’

Like this:

Using the function LOWER

These are three of the simplest functions to use in Excel and they sure come in handy when the need presents itself.

But what about something a bit less straightforward?

Kasper Langmann, Co-founder of Spreadsheeto

Using case functions together
for customization

It’s one thing to learn the basic functions and tools that Excel offers.

But it’s far more fun and fulfilling to piece these together to create our own custom solutions.

That’s exactly what we are going look at now…

Think back to what you learned about abbreviations. That’s right, they tend to create some issues! The three functions you’ve learned about, can’t solve these issues on their own.

Kasper Langmann, Co-founder of Spreadsheeto

Let’s say we want to fix the string ‘fifa world cup’ to read ‘FIFA World Cup’.

This requires us to split the string into separate substrings, namely ‘fifa’, and ‘world cup’.

Here we could use the ‘UPPER’ and ‘PROPER’ functions, respectively.

We could split our original string manually and apply the function we need on each substring.

But then we would need to reunite the results of each separate function to get our final result.

For the sake of demonstration, let’s look at how combining these functions with a couple of others can achieve the same results.

Kasper Langmann, Co-founder of Spreadsheeto

Quick intro to
the functions ‘LEFT’ and ‘RIGHT

For anyone who is not familiar with these two functions, they are very simple in concept and easy to pick up.

Pro-tip: I explain LEFT and RIGHT (+other text functions) in much more in-depth right here

They do exactly what they sound like they do:

They return a substring of characters from the left or right side of our original text string.

Both require one argument: ‘text’.

This is the text or cell reference to apply the function to.

There is also an optional argument: ‘num_chars’. This is the number of characters from the left or right that we want the function to return. (If we omit this argument, the functions return the first character from the left or right.)

The formal syntax for each is as follows:

‘=LEFT(text, [num_chars])’

‘=RIGHT(text, [num_chars])’

Look at the following simple examples to see how these functions work.

Kasper Langmann, Co-founder of Spreadsheeto
LEFT and RIGHT functions

Imagine how this can help us solve our ‘fifa world cup’ problem.

First, we should be aware of something…

… Since the first four letters of our string need to be all capital letters, we want to isolate this part of the original string.

Then we’re going to apply the ‘UPPER’ function to it.

We can isolate those four letters by using ‘LEFT’.

‘=LEFT(“fifa world cup”, 4)’

This gets us the substring result of ‘fifa’.

Since we know this we can enclose the above formula within the ‘UPPER’ function to get ‘FIFA’:

‘=UPPER(LEFT(“fifa world cup”, 4))’

Be mindful of your parentheses when nesting functions like this. It is very easy to overlook a closing parenthesis and this will result in an error. Let’s see how all this looks in Excel.

Kasper Langmann, Co-founder of Spreadsheeto

In the following figure, we have used the cell reference ‘B2’ in the ‘UPPER’ function (cell ‘D2’).

This is the same as ‘=LEFT(A2,4)’ since that is the formula in cell ‘B2’ resulting in ‘fifa’.

LEFT and UPPER functions

The next thing we need to do is extract the remaining substring after ‘fifa’ and apply the ‘PROPER’ function to it. We need to know how many characters from the right we want.

Kasper Langmann, Co-founder of Spreadsheeto

Since the substring ‘world cup’ is 9 characters in length (including the space), our formula will be:

‘=RIGHT(“fifa world cup”, 9)’

In much the same way, the ‘LEFT’ function parsed ‘fifa’ for us, our ‘RIGHT’ function parses out ‘world cup’.

Then like we did with ‘fifa’ we need to enclose our ‘RIGHT’ formula within the ‘PROPER’ function.

LEFT, UPPER, RIGHT and PROPER functions together

Putting it all together requires a bit more finesse.

Join our two substrings by using the ‘&’ and an extra space between them.

Joining LEFT, UPPER, RIGHT and PROPER functions

I know this is starting to get a bit advanced.

My point is to introduce you to the further possibilities. It takes some practice.

But, these advanced techniques are worth learning to get the most out of your letter case functions.

Not all challenges will be as simple as using ‘PROPER’, ‘UPPER’, and ‘LOWER’ on their own. By seeing what is possible, you can leverage these functions as tools for solving bigger challenges.

Kasper Langmann, Co-founder of Spreadsheeto
2019-09-02T13:48:03+00:00