|
Hi - Dave here.
Happy Friday!
A quick email before we take a break for the holidays.
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.
You can see a good example of nesting in the worksheet below, where the goal is to capitalize the first letter in a sentence while leaving all other characters unchanged. There is no built-in function for this in Excel, so we have to write a custom formula like this:
=REPLACE(B5,1,1,UPPER(LEFT(B5)))
Notice that the LEFT function is nested inside the UPPER function which is itself nested inside the REPLACE function. The way to read nested functions is from the inside out:
- The LEFT function grabs the first letter.
- The UPPER function capitalizes the first letter.
- The REPLACE function swaps in the capitalized version.
[ Download the workbook and read the full explanation]
As always in Excel, there is more than one way to do something. My original formula formula for this problem looked like this:
=UPPER(LEFT(B5))&MID(B5,2,LEN(B5)-1)
This formula returns the same result, but it's more complex and requires four different functions. I explain how both formulas work in detail in the article above. Download the worksheet to try them out yourself.
Excel formulas
We maintain a list of over 500 working formulas here.
If you need more structure, we also offer video training.
Happy Holidays and best wishes for the New Year!
Dave
|
