LOWER

What does this function do?

The function converts all applicable characters in the value passed to it to lowercase.

Syntax

=LOWER(text)

Formatting

See PROPER.

LOWER function usage examples

Suppose our task is to make the first letter in each cell uppercase and the rest lowercase. The original table is given entirely in uppercase letters.

Let’s clarify right away – the PROPER function won’t work here because it will capitalize each word if there are multiple words in a cell.

To solve this, we’ll create a complex formula where the MID function will help us. The logic is this – keep the first letter uppercase and make the rest lowercase.

The first letter can be obtained in different ways, either with MID:

=MID(A1, 1, 1)

or with the LEFT function:

=LEFT(A1, 1)

The MID function can also help us extract all letters except the first (here 1000 is a number that definitely exceeds the possible number of characters in a cell):

=MID(A1, 2, 1000)

The final formula will be composed of the above with processing by the LOWER and UPPER functions:

=UPPER(MID(A1, 1, 1)) & LOWER(MID(A1, 2, 1000))

=UPPER(LEFT(A1, 1)) & LOWER(MID(A1, 2, 1000))

As you can see from the example, this combination doesn’t always give the result you’d want when working with formatted text (highlighted in yellow)

After all, it doesn’t actually capitalize the first letters of cells, but capitalizes the first character, which isn’t always a letter. And if there are multiple sentences in a string, the function won’t recognize the second and subsequent ones and won’t capitalize the first characters in them.

Is there a solution to this problem? Yes! But not in Excel’s standard functionality. My add-in offers this functionality. Read more about this feature – capitalize first letters of sentences in Excel.

This post is also available in RU.