A Recursive LAMBDA function in Excel is **useful when you need to create a recursive or iterative solution to a problem.**

Recursive functions solve a problem by first solving smaller instances of the same problem. The LAMBDA function enables us to create these recursive functions ourselves.

##### Table of Contents

The rules for using the `LAMBDA`

function in Excel are as follows:

- The function requires a calculation that you want to execute. Users also have the option to add their own parameters.
- The function can then be used to define a custom function that can be called elsewhere.

Let’s take a look at a problem that can be solved using a recursive solution.

Given a particular string, we would like to remove a certain set of characters. For example, we would like to convert the string** ‘U. S. A.’** to “**USA**”, removing the period and space characters.

If we only wanted to remove one character then the solution is trivial. We can simply use the `SUBSTITUTE`

function to replace the character with an empty string. However, since we’ll be removing a set of characters, we will have to call the `SUBSTITUTE`

method multiple times.

A recursive solution to this would be to create a custom Lambda function that cycles through a set of characters to substitute. The function will call itself if there are still more characters to substitute. Once the last character has been handled, the function should return the final iteration with all indicated characters removed.

Now that we know when to use the `LAMBDA`

function, let’s dive into how to write the function itself.

**The Anatomy of the LAMBDA Function**

The syntax of the `LAMBDA`

function is as follows:

=LAMBDA([parameter1, parameter2, …,] calculation)

Let’s dissect this formula and understand what each of these terms means:

**=**the equal sign is how we start any function in Excel.**LAMBDA()**is our`LAMBDA`

function. It is used to create custom and reusable functions.**parameter1**refers to the first value you want to pass to the function.**parameter2**refers to the second value you want to pass to the function.**calculation**refers to the formula you would like to execute.- Parameters are optional. Users can enter up to 253 parameters in a
`LAMBDA`

function.

**A Real Example of Using a Recursive LAMBDA Function in Excel**

Let’s take a look at a real example of the `LAMBDA`

function being used in an Excel spreadsheet.

In the table below, we created a custom recursive function to clean text. Users simply need to add the characters to remove in cell **D2**. The values in column B are obtained using a named function called RemoveChars.

To get the values in Column B, we just need to use the following formula:

=RemoveChars(A2,$D$2)

The RemoveChars function is defined using the `LAMBDA`

function:

=LAMBDA(data,chars, IF(chars<>"", RemoveChars(SUBSTITUTE(data, LEFT(chars, 1), ""), RIGHT(chars, LEN(chars) -1)), data))

How does this function work? Let’s break it down into a few parts.

The core of the function is a `SUBSTITUTE`

formula that tries to find the left-most character in cell **D2** in a given string.

With each iteration, the formula will remove the leftmost character from the original value in **D2**. This will effectively give us a new character to substitute at each iteration. We can use the `RIGHT`

and `LEN`

functions to do this.

Converting the core formula into a `LAMBDA`

function requires us to generalize our formulas. Instead of specific cell references, we’ll use parameter names. The **data** parameter will refer to the string of text to remove characters from. The **chars** parameter will refer to the set of characters to remove.

This will give us the following formula:

= RemoveChars(SUBSTITUTE(data, LEFT(chars, 1), ""), RIGHT(chars, LEN(chars) -1))

Finally, we need to structure the `LAMBDA`

function to use the RemoveChars function recursively.

All recursive functions must have a point of exit or a condition that stops the recursion. In this case, we want the formula to stop removing characters once the chars parameter is reduced to an empty string.

While the chars parameter is not an empty string yet, the formula should proceed with the recursion.

This is why the final formula has a structure like this:

=LAMBDA(data, chars, IF(chars="", data, RemoveChars(…)))

You can make your own copy of the spreadsheet above using the link attached below.

If you’re ready to try out the `LAMBDA`

function in Excel, let’s begin writing it ourselves!

**How to Use the LAMBDA Function in Excel to Perform a Recursive Solution**

This section will guide you through each step needed to start using recursive `LAMBDA`

functions in Excel. You’ll learn how we can use a recursive implementation of the `SUBSTITUTE`

function to remove an arbitrary set of unwanted characters.

Follow these steps. to start using the `LAMBDA`

function:

- First, look for the
**Name Manager**tool under the**Formulas**tab. The Name Manager will allow us to define our custom Lambda function.

- In the Name Manager dialog box, click on the
**New**button to create a new Name.

- Under the
**Name**input box, enter your desired function name. Set the**Scope**to ‘Workbook’. Finally, enter the desired lambda function into the**Refers to**input box.

- Hit the
**OK**button to add the new function into the Name Manager. The function should now appear in the dialog box.

- Users can now type the formula into the formula bar. Excel even recognizes the function as you are typing it.

- You can now use the custom formula in your spreadsheet. In this example, we’ve also filled in our RemoveChars formula with the parameters indicated in the
`LAMBDA`

formula.

- Fill in the rest of the column with the custom
`LAMBDA`

function by dragging down the first instance we made of the formula.

That’s all you need to remember to start using the `LAMBDA`

function in Excel. This step-by-step guide shows how you can easily create your own custom recursive solution that you can call anywhere in your sheet.

You can now use the `LAMBDA`

functions in Excel together with the various other Excel formulas available to create powerful and efficient spreadsheets.

Are you interested in learning more about what Microsoft Excel can do? Stay notified of new guides like this by subscribing to our newsletter!