UPPER(), LOWER(), PROPER() in Advanced Excel

The UPPER(), LOWER(), and PROPER() functions in Excel are used to change the case of text. These functions help you ensure that text data is in the desired format, which is especially useful for cleaning and standardizing text entries. In this tutorial, we will explore each of these functions with real-time examples.

UPPER() Function

The UPPER() function is used to convert all characters in a text string to uppercase.

UPPER() Syntax

UPPER(text)
  • text: The text string that you want to convert to uppercase.

Real-Time Example of UPPER()

Scenario: You have a list of product names, and you want to standardize all of them to uppercase for consistency in your database.

        Product Name
        apple
        banana
        cherry

Steps:

  1. In cell B1, enter the following formula to convert the product name to uppercase:
    2. =UPPER(A1)
  2. Explanation: This formula converts the text in cell A1 to uppercase. For "apple", the result will be "APPLE", for "banana" it will be "BANANA", and for "cherry" it will be "CHERRY".

LOWER() Function

The LOWER() function is used to convert all characters in a text string to lowercase.

LOWER() Syntax

LOWER(text)
  • text: The text string that you want to convert to lowercase.

Real-Time Example of LOWER()

Scenario: You have a list of email addresses, and you want to standardize them to lowercase for consistency in your records.

        Email Address
        JOHN.DOE@GMAIL.COM
        JANE.SMITH@YAHOO.COM
        MIKE.JONES@OUTLOOK.COM

Steps:

  1. In cell B1, enter the following formula to convert the email address to lowercase:
    2. =LOWER(A1)
  2. Explanation: This formula converts the text in cell A1 to lowercase. For "JOHN.DOE@GMAIL.COM", the result will be "john.doe@gmail.com", for "JANE.SMITH@YAHOO.COM" it will be "jane.smith@yahoo.com", and for "MIKE.JONES@OUTLOOK.COM" it will be "mike.jones@outlook.com".

PROPER() Function

The PROPER() function is used to convert the first letter of each word in a text string to uppercase and the remaining letters to lowercase.

PROPER() Syntax

PROPER(text)
  • text: The text string that you want to convert to proper case (first letter of each word uppercase, the rest lowercase).

Real-Time Example of PROPER()

Scenario: You have a list of customer names, and you want to format them with proper capitalization (first letter of each name capitalized).

        Customer Name
        JOHN DOE
        JANE SMITH
        michael johnson

Steps:

  1. In cell B1, enter the following formula to convert the name to proper case:
    2. =PROPER(A1)
  2. Explanation: This formula converts the text in cell A1 to proper case. For "JOHN DOE", the result will be "John Doe", for "JANE SMITH" it will be "Jane Smith", and for "michael johnson" it will be "Michael Johnson".

Combining UPPER(), LOWER(), and PROPER()

In some cases, you may need to combine these functions to standardize your text data. For example, you may want to convert some data to uppercase, others to lowercase, or format names in proper case.

Real-Time Example of Combining UPPER(), LOWER(), and PROPER()

Scenario: You have a list of customer names and email addresses in mixed cases. You want to standardize the customer names to proper case and the email addresses to lowercase.

        Customer Name   |   Email Address
        JOHN DOE        |   JOHN.DOE@GMAIL.COM
        jane smith      |   JANE.SMITH@YAHOO.COM
        MICHAEL JOHNSON |   mike.jones@OUTLOOK.COM

Steps:

  1. In cell B1, enter the following formula to convert the customer name to proper case:
    2. =PROPER(A1)
  2. In cell C1, enter the following formula to convert the email address to lowercase:
    3. =LOWER(B1)
  3. Explanation: The formula for PROPER() will convert the customer name to proper case, and the formula for LOWER() will convert the email address to lowercase.

Key Differences Between UPPER(), LOWER(), and PROPER()

  • UPPER(): Converts all letters in a text string to uppercase.
  • LOWER(): Converts all letters in a text string to lowercase.
  • PROPER(): Converts the first letter of each word to uppercase and all other letters to lowercase.

Conclusion

The UPPER(), LOWER(), and PROPER() functions are powerful tools in Excel for text formatting. Whether you want to convert text to all uppercase, all lowercase, or proper case, these functions can help you standardize your data. By using them effectively, you can ensure consistency and improve the readability of your text-based data.




Advertisement