MS Excel provides us with various functions to help us customize the case of the text. Among them, the LOWER function is used to change the case of existing text to lowercase. This article will share the complete idea of how the LOWER function works in Excel independently and then with other Excel functions.
LOWER Function in Excel (Quick View)
Excel LOWER Function: Syntax & Arguments
Summary
Converts all the letters in a text string to lowercase.
Syntax
=LOWER(text)
Arguments
| Argument | Required or Optional | Value |
|---|---|---|
| text | Required | Pass the text which will be converted to lowercase. |
Note: All letters in the text are converted to lowercase.
How to Use LOWER Function in Excel: 5 Ideal Examples
The function will return the text string supplied in lowercase.
1. Convert a Text into Lowercase Using LOWER Function
Let’s have a product list with their ID, NAME, and Price. All the product name is in capital letters. Now our task is to convert all the product names into lowercase using the LOWER function.
STEPS:
- Firstly, select the cell where you want to put the formula. In our case, we select cell E5.
- Secondly, enter the formula into that cell.
=LOWER(E4)- Thirdly, press Enter.
- Drag the Fill Handle icon down to duplicate the formula over the range. Or, to AutoFill the range, double-click on the plus (+) symbol.
- Finally, you will able to see that all the letters are converted to lowercase.
2. Change Alphanumeric Text into Lowercase in Excel
Let’s assume our ID for each product contains alphanumeric values. The alphabets on the IDs are in uppercase form. Now our task is to convert all the alphabets into lowercase.
STEPS:
- To begin with, choose the cell E5 and enter the formula in the cell.
=LOWER(E5)- Then, hit the Enter button on your keyboard.
- Likewise, in the previous example, to copy the formula over the range, drag the Fill Handle symbol downward. Alternatively, you can double-click the addition (+) sign to AutoFill the range.
- Thus, you can the result in column E.
3. Create Email Addresses from Names Using Excel LOWER Function
Sometimes we may need to create emails using names for any organization or group. Let’s consider we have a dataset of employees with their ID, First Name, and Last Name. Now, our task is to create emails for these employees using their names.
STEPS:
- In the first place, input the following formula in cell E5.
=LOWER(B4&"."&C4&"@"&"xyz.com")- Further, press Enter.
- Consequently, copy it down to E10.
- Lastly, the result will appear in that column.
Formula Explanation
In the parameter of this LOWER function ampersand (&) is used to concatenate formula and text.
4. Transform Dates into Lowercase Using LOWER Function
By default, the LOWER function is not capable of changing dates from upper to lower format. But this is possible using another function with this LOWER function named TEXT function. Let’s see how we can change the format of the product’s delivery date.
STEPS:
- Likewise, in the earlier examples, select cell E5 and enter the formula into that cell.
=LOWER(TEXT(C4,"mmmm d, yyyy"))- Hit Enter.
- After that, drag the Fill Handle icon to copy the formula over the range. Or, double-click on the plus (+) sign. This also duplicates the formula.
- Subsequently, the date will convert to lowercase.
Formula Explanation
- TEXT(C4,”mmmm d, yyyy”) this portion of the formula converts the dates of the C column into text. Where the format of the text is “mmmm d, yyyy”.
- Lastly, the LOWER function converts the text into lowercase.
5. Convert Text with Range in Excel
In this example, we will see that we can easily use the range in the LOWER function in excel. Let’s follow the instructions.
STEPS:
- To begin with, choose the cell E5 and input the formula there.
=LOWER(C5:C10)- Then, press Enter.
- And, Lastly, the names will automatically change into lowercase in the range.
Alternatives to Excel LOWER Function: Flash Fill Feature and VBA LCase
1. Flash Fill to Change Text into Lowercase in Excel
Excel provides the option of using flash fill which made our task easier. Let’s assume we need to convert all the names using flash fill.
STEPS:
- In the beginning, type the first cell text in lowercase format manually.
- Then, go to the second cell and try to type the name. There you will get suggestions like this.
- Finally, press Enter and see the output.
2. VBA Code to Convert Text into Lowercase in Excel
Using VBA code, we can easily make our tasks more efficient and automated. Here we will see how to use VBA code to convert any text into lowercase. We will convert all the employee names into lowercase.
STEPS:
- Firstly, select the names that you want to change.
- Further, go to the Developer tab and select the Visual Basic option.
- Then, select Module under the Insert option.
- Consequently, paste the code into the VBA window.
Code:
Sub Lowercase()
For Each Cell In Selection
If Not Cell.HasFormula Then
Cell.Value = LCase(Cell.Value)
End If
Next Cell
End Sub
- After that, Run the code (Shortcut F5).
- Finally, go to the worksheet and see the output.
Code Explanation
- For Each Cell In Selection using this, we are iterating through the selected cells.
- If Not Cell.HasFormula Then this portion is checking whether it is a selected cell or not.
- Cell.Value = LCase(Cell.Value) this part converts them into lowercase using the LCase function.
- End If this terminates the if statement.
Common Errors While Using Excel LOWER Function
| Common Errors | When they show |
|---|---|
| #NAME | This will appear if you did not enter the function name properly. |
| #REF! | It will appear if a LOWER function formula is used between two different workbooks and the source workbook is closed. |
Download Practice Workbook
Conclusion
That’s all about the LOWER function. Here I have tried to give a summary of this function and its different applications. I have shown multiple methods with their respective examples but there can be many other iterations depending on numerous situations. If you have any inquiries or feedback, please let us know in the comment section.
<< Go Back to Excel Functions | Learn Excel
