Text functions in Excel formulas: UPPER, LOWER and PROPNOUN

Excel offers its users a total of 3 functions for working with uppercase and lowercase letters in text. These text functions either convert letters to uppercase or lowercase or change only the first letter in a word to uppercase.



Formulas with Excel text functions

Let's first examine the 3 Excel text functions with examples:

1. UPPER - this text function changes all letters in a word to uppercase.
2. LOWER - this function converts all characters in the text to lowercase.
3. PROPNOUN - the function changes only the first letter in each word to uppercase.

As seen in the example in the image, these functions require only the original text data in their arguments, which should be transformed according to the user's requirements.

Despite the wide range of functions in Excel, there is still a need for a function that can capitalize only the first letter in a sentence, not in every word. However, to solve this task, you can create a custom formula using the same and other Excel text functions:

=UPPER(LEFT(A1,1))&LOWER(RIGHT(A1,LEN(A1)-1))

To solve this common task, additional Excel text functions are used in the formula: LEFT, RIGHT, and LEN.

Principle of the formula for replacing the first letter in a sentence

If you carefully look at the syntax of the formula mentioned above, you can easily notice that it consists of two parts connected by the operator &.

The left part of the formula uses the additional function LEFT:

The task of this part of the formula is to change the first letter to uppercase in the original text string in cell A1. Thanks to the LEFT function, a specific number of characters can be obtained starting from the left side of the text. The function requires filling in 2 arguments:

1. Text - a reference to the cell with the original text.
2. Number_of_characters - the number of characters returned from the left side (from the beginning) of the original text.

In this example, only the first character from the original text string in cell A1 needs to be obtained. Then, the obtained character is transformed into an uppercase letter.

The right part of the formula after the & operator works in a similar way to the left part, but it solves a different task. Its task is to convert all characters in the text to lowercase but without changing the first uppercase letter, which is handled by the left part of the formula. Instead of the LEFT function, the RIGHT function is applied in the right part of the formula:

The RIGHT text function works inversely proportional to the LEFT function. It also requires filling in two arguments: the original text and the number of characters. However, in this case, we cannot specify a fixed value as the second argument. After all, we do not know in advance the number of characters in the original text. Moreover, the length of different original text strings may vary. Therefore, it is necessary to calculate the length of the text string in advance and subtract -1 from the obtained numerical value to avoid changing the first uppercase letter in the string. After all, the first letter is processed by the left part of the formula and is already transformed according to the user's requirements. Therefore, no function from the right part of the formula should affect it.

To automatically calculate the length of the original text, the Excel text function - LEN (length of string) is used in the formula. This function requires only one argument to fill - a reference to the original text. As a result of the calculation, it returns a numerical value. Therefore, after the function =LEN(A1), we subtract -1. This allows us not to affect the first uppercase letter with the right part of the formula. As a result, the RIGHT function returns a text string without the first character for the LOWER function, which replaces all characters in the text with lowercase letters.

As a result of combining both parts of the formula with the & operator, we get a beautiful text sentence that, according to the rules, starts with the first uppercase letter. And all other letters are lowercase until the end of the sentence. Regardless of the length of the text, using the same formula gives us the correct result.

Practical example of using UPPER and LOWER formulas

Let's provide an example for the practical application of a formula using the text functions UPPER and LOWER. In traditional writing styles, an exclamation mark is used at the end of a sentence to emphasize the importance of the content with emotional intensity. In modern style, uppercase letters are still used. In conversational messaging, a construction like "Don't RAISE your voice at me!" has even become common. For example, we need to emphasize the last word in a sentence. With just an exclamation mark, we emphasize the entire sentence, but with uppercase letters, we can highlight a specific word.

To automatically emphasize the last words in each sentence with uppercase letters, we can create an Excel formula. To keep this smart formula from being too complex, let's divide it into two consecutive formulas. The first formula will extract the last word in each sentence with an exclamation mark at the end and display it in uppercase in a separate column with the header "Accent":

=IFERROR(UPPER(TRIM(RIGHT(SUBSTITUTE(LEFT(A2,FIND("!",A2))," ",REPT(" ",LEN(A2))),LEN(A2)))),"")

Next, we use the second formula, which searches for the emphasized text in the original sentence and replaces it with uppercase letters. Since the text in the "Accent" column is already in uppercase, and in the sentence, it is in lowercase, the SUBSTITUTE function won't find it unless we first convert it to lowercase using the LOWER function:

=IF(LEN(B3),SUBSTITUTE(A3, LOWER(B3), B3),"")

Now all formulas work based on the presence of an exclamation mark in the original sentences. If there is an exclamation mark at the end of a sentence, the last word will be emphasized in uppercase letters.

Download

Moreover, this smart formula can emphasize letters for any word after which an exclamation mark is placed, even if the exclamation mark is not at the end of the sentence but in the middle.