Nest Excel Formulas to Reduce Workbook Clutter
/Combining several separate Excel formulas together into one more complex formula reduces clutter in your workbooks and make them much easier for others to read. Excel beginners often use one cell to hold a formula, another to hold a second, and then perhaps a third to refer to results of formulas one and two. Adding a number of extra columns to a workbook for the express purpose of holding extra "helper formulas" is a fast way to work, but workbooks can quickly get out of control.
I recommend combining multiple formulas together into a single, more complex formulas where possible. Here is a combination that appears in many of the workbooks on which I work.
Left and Find
When I'm given a last name followed by a comma and then a first name, I use the Left function to extract that last name. For example, Smith, John is something I might see. In fact, I often see a whole column filled with last names and first names. If everyone had a last name that was five characters long, then the formula would be easy: =Left(B2,5). However, when last names are different lengths, then we must rely on the position of the comma to help us discover how many characters we need to extract. The formula =Find(B2,",") will return the position of the comma in our example. The answers is six. We don't want to return the comma, so we need the position of the comma less one. Our combined formula is =Left(B2,Find(B2,",")-1).
You might ask whether you can just use Text to Columns to split data across multiple columns. You can. However, this approach allows you to leave your original data intact, which you might elect to do based on the project at hand.
Read more about Text to Columns.
If you use vLookup a lot, you've probably seen it return #N/A when it cannot find the result you seek. Consider enclosing the whole vLookup formula within IfError, which will allow you to set a custom phrase or alternate formula to use when vLookup give you an error instead of a result you want.