You may think it is impressive building complex, long Excel formulas in a single cell, but from a spreadsheet best practice perspective this just introduces more risk.
The longer the formula, the more chance of errors and the more difficult it is to audit/ understand/ use. Even the developer may struggle to understand the complex formula a day or two later.
Which would you prefer to have to review (these formulas below do the same thing, but one is built in a single cell and the other is over 3 cells)
Single Cell
=MID($A12,FIND(“#”,$A12)+1,FIND(“#”,$A12,FIND(“#”,$A12)+1)-FIND(“#”,$A12)-1)
Three Cells
=FIND(“#”,$A12)
=FIND(“#”,$A12,E12+1)
=MID($A12,E12+1,G12-E12-1)
Although they achieve exactly the same thing, we promise you it is easier to understand and fix the second option.
Use short, concise Excel formula over many cells
If you have to develop complex formulae, try and spread them over a number of rows or columns, so that the calculations are easily understood and can be recalculated without trying to understand a monster formula with so many brackets that you don’t know which applies where.
It will not affect the speed or size of your spreadsheet, but it will improve its simplicity and robustness.
In the video clip below, see the outcome of using the different approaches (and keep in mind that even the complex formula used is actually one of the simpler ones- there are much worse sitting on your spreadsheets which you are probably avoiding)
To follow along go to 1.8