# How to Write a Recursive LAMBDA Function in Excel

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.

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:

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

2. In the Name Manager dialog box, click on the New button to create a new Name.
3. 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.
4. Hit the OK button to add the new function into the Name Manager. The function should now appear in the dialog box.
5. Users can now type the formula into the formula bar. Excel even recognizes the function as you are typing it.
6. 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.
7. 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!

### Get emails from us about Excel.

Our goal this year is to create lots of rich, bite-sized tutorials for Excel users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

Read More

## How to Forecast Cash Flow in Excel

This guide will explain how to forecast cash flow in Excel. A forecast cash flow is a useful…
Read More

## How to Fix Wrap Text Not Showing All Text in Excel

This guide will discuss how to fix wrap text not showing all text in Excel using three easy…
Read More

## How to Lookup a Value in a 3D Table in Excel

This guide will explain how you can use Excel functions to lookup values in a 3D table in…
Read More

## How to Paste Visible Cells Only in Excel

This guide will explain how to paste to visible cells only in Excel. By default, selecting a range…
Read More

## How to Perform Sensitivity Analysis for Capital Budgeting in Excel

This guide will explain how to perform sensitivity analysis for capital budgeting in Excel. Sensitivity analysis is a…
Read More

## How to Convert Decimal to Days Hours and Minutes in Excel

This guide will explain using the TEXT function to convert decimal values into the equivalent number of days,…