Explanation
One of the most important skills to learn with Excel formulas is the concept of nesting.
Put simply, nesting just means putting one function inside another.
Nesting is super useful, but it does take some practice.
You have to learn to read a formula from the inside out.
The formulas below are good examples of nesting.
Practice reading the formulas starting with the innermost functions.
This article explains a few approaches to the problem, including the formula featured in the worksheet above.
What about the PROPER function?
However, it won’t work in the worksheet shown because PROPER will capitalizeall words.
We need a different approach.
But do we need four functions to perform this task?
Can’t we do better?
Yes, we can.
The REPLACE does the work of replacing the first letter in place, leaving all remaining characters unaffected.
Note: If you do want to force all remaining characters to be lowercase, see the modification below.
The only difference is that the REPLACE function will begin with an entirely lowercase text string.
For example =REPLACE(“XYZ123”,4,3,“456”) returns “XYZ456”.
UPPER Function
The Excel UPPER function converts a text string to all uppercase letters.
Numbers, punctuation, and spaces are not affected.
For example, =LEFT(“apple”,3) returns “app”.
For example, =MID(“apple”,2,3) returns “ppl”.
LEN will also count characters in numbers, but number formatting is not included.
