LET Function in Excel write meaningful formulas that work faster
The brand new LET function in Excel allows us to declare variables and intermediate calculations inside a formula to create meaningful formulas that work faster.
When the same expression is used multiple times inside a formula, Excel will recalculate it multiple times. LET function allows us to name expressions which can be called multiple times thereby reducing the calculation time.
In the following example, LET function is used to shorten the length of a popular Excel formula which will extract the numbers from an alphanumeric code.
=LET(X,ROW(INDIRECT("$1:$"&LEN(B3))),IF(SUM(LEN(B3)-LEN(SUBSTITUTE(B3,{"0","1","2","3","4","5","6","7","8","9"},"")))>0,SUMPRODUCT(MID(0&B3,LARGE(INDEX(ISNUMBER(--MID(B3,X,1))X,0),X)+1,1)10^X/10),""))The length of this classic formula is ‘283’ characters.
If you see the formula carefully, you will notice that the expression ‘ROW(INDIRECT(“$1:$”&LEN(B3))),1))’ is repeated 3 times. And this is where we can make use of LET function.
So, I have wrapped the entire formula using LET function, have assigned the expression ROW(INDIRECT(“$1:$”&LEN(B3))),1)) to a variable ‘X’ and has replaced this expression with ‘X’ at every location in the formula.
=LET(X,ROW(INDIRECT("$1:$"&LEN(B3))),IF(SUM(LEN(B3)-LEN(SUBSTITUTE(B3,{"0","1","2","3","4","5","6","7","8","9"},"")))>0,SUMPRODUCT(MID(0&B3,LARGE(INDEX(ISNUMBER(--MID(B3,X,1))X,0),X)+1,1)10^X/10),""))
The length of the formula is now ‘206’ characters and this modified formula is faster than it was earlier.
