Use UPPER LOWER Function in Excel - convert text from upper to lower case or from lower to upper case

Hi friends,

Today in this article I am going to show you two Microsoft Excel functions that are very useful if you need to convert texts from one case to another i.e., from upper to lower and vice versa. We'll discuss about these two functions and we shall use these function combined to get desired result by the help of examples. So, let us discuss all these three functions step by step, one after another.

How to use the UPPER Function

Description

The Microsoft Excel UPPER function allows you to convert all text to all uppercase.

The UPPER function is a built-in function in Excel that is categorized as a String/Text Function. The UPPER function can be entered as part of a formula in a cell of a worksheet.

Syntax

The syntax for the UPPER function in Microsoft Excel is:

=UPPER(text)

Parameters or Arguments

text

The string that you wish to convert to uppercase.

Example UPPER Function

Let's look at some Excel UPPER function examples and explore how to use the UPPER function as a worksheet function in Microsoft Excel:

Just type '=' sign and UPPER and then select the cell that you want to convert to upper case.

This function works fine in with numbers, spaces, special characters etc. Very simple function, the below picture will make it very clear.

How to use the LOWER Function

Description

The Microsoft Excel LOWER function converts all letters in the specified string to lowercase. If there are characters in the string that are not letters, they are unaffected by this function.

The LOWER function is also a built-in function in Excel that is categorized as a String/Text Function. The LOWER function can be entered as part of a formula in a cell of a worksheet.

Syntax

The syntax for the LOWER function in Microsoft Excel is:

=LOWER(text)

Parameters or Arguments

text

The text/string to convert to lowercase.

Example LOWER Function

This function also works similar to UPPER function only the case is reversed, lower case. Let's look at Excel LOWER function example and explore how to use the LOWER function in Microsoft Excel:

Just type '=' sign and LOWER, hit tab in your keyboard and then select the cell that you want to convert to lower case.

This function also works fine with numbers, spaces, special characters etc. Very simple function, Lets' have a look at the below picture.

Now we'll try to change names in exact case from random upper or lower case, i.e., the characters in the name field could be in any case but  we'll capitalize the first character and the rest in lower case. So, lets' begin.

In the below example I've taken few names in first column and the next three columns are to convert it into upper, lower and in the last one to get the desired result, first character to be capitalized and the rest should be in lower case.

To get the output what I have done is first I converted the first character in capital letter using UPPER and LEFT function, then used LOWER and used LEN function to convert all the letters in lower case except the first one, and finally I combined these functions using '&' operator, you can use CONCATENATE function.

Lets break this example into step by step.
    1.    Use UPPER and LEFT function to convert first character into Capital Letter. Formula is =UPPER(LEFT(D3,1)) then
    2.    Get the remaining characters i.e., except first letter. Use RIGHT(D3,(LEN(D3)-1)), RIGHT function will take the characters from right side, LEN functions decides the number of characters in RIGHT function.
    3.    Cover it by LOWER function, formula now is            LOWER(RIGHT(D3,(LEN(D3)-1)))
    4.    Lastly, combine these two functions by & sign or use CONCATENATE function. The final formula is : =UPPER(LEFT(D3,1))&LOWER(RIGHT(D3,(LEN(D3)-1)))

Now, we have got one formula to capitalize the first character of a word, but what about a name space in between first and last name or middle name or one big paragraph and if we need to capitalize each and every first character of every word ?

Watch my next article on this problem. Hope you have enjoined a lot reading this article, thank you for reading...