When you work with Excel, sometimes you may receive data that is not very consistent when it comes to lower and upper case letters. For this reason, Microsoft introduced a few functions that will help you deal with this problem.
The first letter in each word
The first function is PROPER. This function will capitalize the first letter of each word and will keep other letters lowercase. Take a look at the following example.
The PROPER works no matter what combination of letters you will find inside column B.
In the next example, I’ll show how we can achieve the capitalization of the first letter in a sentence.
The first letter in a sentence
This time, we can’t use PROPER because we only need to capitalize the first letter of the first word and not the first letter of each word.
Analysis of the formula:
- The function LEFT(B3, 1) takes the first letter from cell B3.
- UPPER(LEFT(B3, 1)) capitalizes the first letter.
- The function REPLACE replaces one letter (starting from the first one) with the capitalized letter.
This formula works well when all letters are lowercase. The problem starts when the case is not consistent. For example.
In the next part of the lesson, we will deal with this problem.
Capitalize the first letter and make other letters lowercase
In order to fix the problem, we need to tweak our function a bit.
|
1 |
=REPLACE(LOWER(B3),1,1,UPPER(LEFT(B3,1))) |
This function not only makes sure that the first letter is uppercase but also converts all other letters to lowercase.