9. Dynamic Arrays

9.20 Recursive LAMBDA and a Small Reusable Function Library

By Ravindra Bagale · Last updated: 6 October 2026

Example first. Rani receives amounts typed in many styles: "Rs. 1,299/-", "Rs 64", "1,299/-". She wants one function that removes a list of unwanted characters, one character at a time, until nothing is left to remove. A function that calls itself with a smaller job each time is called recursive. In Excel, a LAMBDA can call itself only when it has a name in Name Manager. We will build STRIPCHARS, then collect the functions from this chapter into a small library workbook.

First look at the data (before → after):

Expected result — Column B after filling down

Name: STRIPCHARS
Refers to: =LAMBDA(text,chars,IF(chars="",TRIM(text),STRIPCHARS(SUBSTITUTE(text,LEFT(chars),""),MID(chars,2,999))))
B2: =VALUE(STRIPCHARS(A2,"Rs.,/- "))   (fill down)

Tracing STRIPCHARS by hand (this is the best way to understand recursion)

Every recursive function needs two parts:

1. A stop condition that returns an answer without calling itself: IF(chars="",TRIM(text), …).
2. A smaller job passed to the next call: MID(chars,2,999) is chars without its first character, so it gets shorter every time and the stop condition is sure to be reached.

Note: SUBSTITUTE is case-sensitive, so "R" removes only the capital R. Be careful with letters: removing "s" also removes every small s in the text. For text with words in it, the REDUCE method from lesson 9.19 or REGEXREPLACE from lesson 9.12 is often safer.

A second example: weeks to reach a target. Pune sells ₹520 thousand a week (fictional). If sales grow 5% a week, after how many weeks do they reach ₹700 thousand?

WEEKSTOTARGET = LAMBDA(sales,target,growth,IF(sales>=target,0,1+WEEKSTOTARGET(sales*(1+growth),target,growth)))

=WEEKSTOTARGET(520,700,5%) returns 7. The sales path is 520.0, 546.0, 573.3, 602.0, 632.1, 663.7, 696.8, 731.7. Check without recursion: =ROUNDUP(LN(700/520)/LN(1.05),0) also gives 7.

Your reusable function library

Make one workbook called My Functions.xlsx with these names (all from this chapter), a Tests sheet with the calls below, and a short note in each name's Comment box:

CITYTOTAL uses a Table named tblOrders (chapter 6), so it keeps working when rows are added.

How to reuse the library in another workbook

1. Open My Functions.xlsx and the target workbook.
2. Right-click the Tests sheet tab › Move or Copy… › choose the target workbook › tick Create a copy › OK.
3. The names used on that sheet come with it. Check Formulas › Name Manager in the target workbook.
4. Delete the copied Tests sheet if you do not want it; the names stay.

(Microsoft also offers a free add-in called Excel Labs with an "Advanced formula environment" for writing and importing LAMBDA libraries. It is optional; everything above works with Name Manager alone.)

Common problems

How these results were checked: LAMBDA helper functions do not run in LibreOffice, so each expected value was computed in Python and the totals were cross-checked in LibreOffice with classic formulas (SUMIF, running SUM, SUMPRODUCT). Microsoft reference: LAMBDA function.

Practice task (try it before you open the answer):

1. Create a recursive named LAMBDA REPEATTEXT(text,n) that returns the text n times joined with a space, stopping when n = 1 (for example "Pune Pune Pune").
2. How many weeks does Nashik need to grow from 330 to 400 at 4% a week? Use WEEKSTOTARGET.
3. Check your answer to step 2 with ROUNDUP and LN.