As an alternative to the default Change Case button, Excel has various in-built functions dedicated to formatting capital or small letters. For Instance, PROPER, LOWER, UPPER, etc. In the previous guide, we learned all about the PROPER function to capitalize the initial letter.
Today, we will explore the LOWER function which converts all letters into a lowercase. So, if you’ve been manually typing words in lowercase, stop doing it and opt for the LOWER function.
In Excel, lowercase texts can be useful when you’re splitting columns By Lowercase to Uppercase. Or, simply to create a uniform format for your records. Nonetheless, for more dynamic applications, you could nest the LOWER function with other Excel functions.
Arguments for LOWER Function
Excel’s LOWER function returns all the uppercase text strings in lowercase. This function is very easy to use as you just need to enter a text or a cell reference in the formula. Here, the LOWER function returns any punctuations in a text string as it is. But, for numerical values, this function changes its formatting to text.
Examples of LOWER Function
Example 1: Using Text String in Formula
You can directly enter a text reference in the LOWER function. But, you must double-quote the words when using the text reference. Your formula should look something like this
Let’s say you have viBIN vEntures word to convert to lowercase. For this, the formula would be
The formula will change all of the letters to lowercase and return “vibin ventures.”
Remember, whenever you pass down a text reference without double quoting them in any formula in Excel, it’ll result in #NAME? error. This applies to the LOWER function too.
Example 2: Using Cell Reference in Formula
For larger datasets, I suggest you use the cell references in the formula. When you pass down a cell reference in the LOWER function, you’ll be able to take advantage of Excel’s Flash-fill feature. This just cuts the tedious process of entering each word and double quoting them every time.
Suppose, you need to convert the values of Column D into lowercase. To do this, you could type in the formula mentioned in the box and press enter. Then, extend the plus (+) cursor for the other cells, also known as Flash-fill.
In cell D2, all the letters of text strings are in uppercase which is “COMPLETED.” The LOWER function changes them into small letters and returns “completed” as a result.
Alternatively, you can also use cell ranges in the LOWER Function. Again, taking the same example, we entered the followinf formula. This time, we’re using cell ranges.
Nest LOWER Function With Other Functions
CONCAT and LOWER
One of the cases, when you need to use the CONCAT function, could be to join the username and domain address to form a valid email id. Suppose your manager asked you to create the address in all lowercase. To do so, you could nest the CONACT and LOWER functions together. Your formula would be
In the formula, CONCAT(A2,B2) will first join the text of A2 and B2. Then, it’ll return [email protected]. Now, the LOWER function will convert the result into lowercase and give you “[email protected]” output.
LOWER and TEXT
When you attempt to convert the dates into lowercase using just the LOWER function, it will return the dates in serial number format. To address this issue, you can nest the TEXT function inside the LOWER function.
In the given example, we have Dates in Column B. To return these dates in lowercase, we used this formula
=LOWER(TEXT(B11, "d, mmmm, yyy"))
Firstly, the TEXT function returns the value of B11 in day, month, and year format. Then, the LOWER function converts the text into small letters. You will have 5, september, 2023 as a final result.
How to Avoid Errors When Using LOWER Function?
You will most likely encounter errors while using a LOWER function in Excel if you’ve entered a wrong argument in the formula. It could be an inaccurate cell reference, missing null references, and many more. But, one of the most common mistakes users make while entering the LOWER function is with the double quotation mark. So, to avoid #NAME? Error, make sure you are double-quoting the text reference.