Learning how to Capitalize in Excel can be super handy in a range of scenarios. When working with strings of text in Excel, sometimes the overall formatting of the data we are working with isn’t ideal. Luckily, we can very easily run functions to perform tasks including capitalizing in Excel.
In this simple guide, we are going to run through a few techniques on how to capitalize in Excel.
We will cover:
- Capitalise the first letter of each word with PROPER
- Using UPPER and LOWER to capitalize specific parts of a string
Let’s get started with the first and easiest approach.
Capitalize in Excel using PROPER
The first function we are going to work with is called PROPER. There are two scenarios where this one comes in super handy. The first, and most common is if we just have a whole lot of cells with single words in them and we want to capitalize the first letter. Alternatively, if we have a set of words where we want to capitalize every word then this one works nicely too. An example of this would be someones full name – where we want the first, middle and last name to all start with capital letters.
The formula for this one is super simple:
=PROPER(cell)
Let’s look at some examples, along with the outputs.
As we can see, when working with someones name it easily adds the capital J. When working with a couple of words where the entire second word is in capitals it reverts it back to just the W being a capital, and with longer sentences too it nicely turns every first letter of each word capitalized.
The PROPER function works very well with these examples, but what if we wanted our sentences to look more readable when we have lots of words? This is where we move onto our second technique on how to capitalize in Excel.
Using UPPER and LOWER to Capitalize
This one is a little more complicated, as it essentially involves combining multiple formulas – using a UPPER, and a LOWER formula at the same time with specific criteria, utilizing the LEFT and RIGHT functions.
Let’s say we just wanted to capitalize the first letter of the entire sentence, the formula would look like this (assuming the cell we are working with is in B3):
=UPPER(LEFT(B3,1))&LOWER(RIGHT(B3,LEN(B3)-1))
Before we show off some examples, let’s break this one down as there is a bit going on.
Starting with the first of the two formulas: UPPER(LEFT(B3,1)
- UPPER – the function to convert to upper case
- LEFT(B3,1) – starting with the left most part of our string, in this case whatever is in B3, the 1 narrows down to just the very first character
The & symbol simply ties our formulas together, essentially concatenating the output of them.
Moving on to the second half of our formula: LOWER(RIGHT(B3,LEN(B3)-1))
- LOWER – the opposite of UPPER – the function to turn whatever we select into lower case
- RIGHT – starts at the very right and works its way back the specified number of characters
- LEN(B3-1) – takes the LENGTH of the string in cell B3, and minus one means that when counting characters basically dont count the last one – which since we are starting from the right means the first characters
With all of this combined we have the very first character capitalized, with every single other character set to lower case!
Using the exact same three examples from earlier, this would result in the below:
As mentioned, this one is a little more complicated, but if the data we are working with to clean up in Excel is made up of longer sentences it would make a lot more sense to use something like the UPPER and LOWER combination.
This sums up our guide on how to Capitalize in Excel in two different ways. For more handy guides on working with Excel, be sure to check out our Excel Tips page.