Quality Assurance

Explain lower(), upper() and proper() functions in Spreadsheets

Anju.r | | 2 min read

We often have to edit all the values in an entire column of the spreadsheet we are working on. This can be not easy and time-consuming. Suppose you work in OpenOffice/LibreOffice Calc or Google Sheets and need to convert an employee's name in three cases.

  1. Convert all letters to upper-case.
  2. Convert all letters to lowercase.
  3. Capitalise the first character of each word.

Doing this manually will take a lot of time if you work on documents with thousands of rows. So lets us check how to accomplish it using function. Please check the below requirement to understand it better.
Requirement: Your name is in the column A, and you are converting the value to column B.

Case 1: Convert all letters to upper-case.

We can use UPPER() to accomplish this task. It will convert a text string to upper-case.


Syntax is 

UPPER(text)


Then in the first cell of the Column B, we can enter the function =UPPER(A1). This will convert the value. Column A1 value= Tester test, then column B1 value becomes "TESTER TEST"

Case 2: Convert all letters to lowercase. We can use LOWER() to accomplish this task.


Syntax is 

LOWER(text)


It will convert a text string to a lowercase. In the above example, the column B value becomes "tester test" if we enter the function =LOWER(A1) in column B1.

Case 3: Convert the first character of each word capitalised. 

We can use PROPER() to accomplish this task. It will convert the first character of each word capitalised.


Syntax is 

PROPER(text)


Column B1 value becomes "Tester Test" if we enter the function =PROPER(A1) in column B1

I hope this article helps you to understand all the three functions. These functions are tested and working on LibreOffice Calc and Google Sheets. This will probably work on other spreadsheet systems as well.

 

 

Related Topics