How to Capitalise the First Letter in Microsoft Excel

There are various functions for changing the case of text in a cell. UPPER, LOWER and PROPER are all incredibly useful functions, particularly when you have columns of data in various formats.

Using Upper and Lower Case

UPPER and LOWER convert any text to the corresponding case. PROPER capitalises each word (useful for names etc.), however there is no "sentence" case (as you get in Microsoft Word) which capitalises the first letter but leaves the rest as lower case.

How to Capitalise Only the First Letter

It is possible to use a number of different formulas to achieve this, but unfortunately, it's not as straightforward as using just a single formula.

First, you will need to capitalise the first letter. Let's assume our text is in cell A1.

=UPPER(A1) - will simply convert all the text to upper case. We only want the first letter, so we need to use another formula to help us.

=LEFT(A1, 1) - will look at cell A1 and take the first letter from the left. We need to combine these, so the first part of the formula is: =UPPER(LEFT(A1,1))

Next, you need to convert the rest of the text to lower case. The tricky part here is that we don't necessarily know how many characters we need, unlike when we used the LEFT function previously.

=LOWER(A1) - will convert all the text to lower case. We want everything except the first letter, so we need the RIGHT function.

=RIGHT(A1, ?) - will take characters from the right hand side of cell A1 but since we don't have a fixed number of letters, we can't put a number in where the "?" is displayed. Instead, we will need to ask Excel how many letters there are in total and then minus one (the one we already used as a capital letter).

=LEN(A1) - will tell you how many characters there are in cell A1. So, to convert the remaining text to lower case, the formula will be:
=LOWER(RIGHT(A1,LEN(A1)-1))

To join the two together (and make the first letter upper case and the remaining text lower), you just need to use an ampersand. Your complete formula will be:
=UPPER(LEFT(A1,1))&LOWER(RIGHT(A1,LEN(A1)-1))



Quite a long formula but think of all the time it might save!