UPPER

What does this function do?

This function allows converting lowercase (small) letters to uppercase (capital), in other words, converting them to upper case. Characters already in upper case and characters for which this is not applicable remain unchanged.

Syntax

=UPPER(text)

Formatting

See PROPER

First words of cells entirely in uppercase (“caps”)

This is most often useful when working with full names in Excel. Surnames are usually written entirely in uppercase, while first and middle names are simply capitalized.

Let’s assume that in the original list all words are already capitalized. Then the formula will require getting the first word of the cell (in this case the surname), converting it to uppercase and adding the remaining words (first and middle names) to it.

The formula will look like this:

=UPPER(LEFT(A1, SEARCH(" ", A1))) & MID(A1, SEARCH(" ", A1) + 1, 1000)

Capitalize the first letter of a cell

It might seem simple, but finding that very first letter in a cell is quite a problem that deserves its own article. Here’s the final array formula from there:

{=MATCH(FALSE, EXACT(MID(LOWER(A1), ROW(INDIRECT("1:" & LEN(A1)), 1), MID(UPPER(A1), ROW(INDIRECT("1:" & LEN(A1)), 1)), 0)}

Capitalize first letters of sentences in Excel

The formula above will capitalize only one, the first letter of the cell.

What if we want to capitalize the first letters of sentences, of which there may be several in a cell? This formula won’t identify the first letter of the second sentence and beyond, and therefore won’t solve the problem.

Is there a solution? Yes! It’s offered by my !SEMTools add-in for Excel. Read more here – change letter case in Excel.

This post is also available in RU.