Excel LAMBDA function, Examples

Content

Introduction

This article explains the basic principles of how to create your own user-defined functions using the LAMBDA function

Name of worksheet in a cell

What makes the LAMBDA function useful is that it makes it easier to use complicated formulas. A good example is the formula to place the name of the current worksheet into a cell. Normally, one uses the CELL function for this:

=CELL("address",A1)

This gives you a result which looks like this:

C:\Users\piete\Documents\[Lambda Demo JKP.xlsx]Lambda Explanations

In this example, the name of the worksheet is "Lambda Explanations". To remove the preceding information, we must find the closing square bracket and take all text after that:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,LEN(CELL("filename",A1)))

Ugly and cumbersome. Let's use the LET function to make this easier:

=LET(f,CELL("filename",A1),MID(f,FIND("]",f)+1,LEN(f))

Now we're just two steps away from transforming this into a Lambda.

1. Add the LAMBDA function (the only argument is a cell on the worksheet from which you want to display the name)

=LAMBDA(cl,LET(f,CELL("filename",cl),MID(f,FIND("]",f)+1,LEN(f)))

2. Define a name called SheetName:

The SheetName name 

That's it, you can now get the sheet name into a cell with this simple formula:

=SheetName(A1)

Download the accompanying file SheetNameLambda.xlsx (downloaded 105 times)

Justifying text

You may be aware that you can have Excel split long text across multiple rows; simply use the Fill, Justify button.

Have a look at this spreadsheet:

Long text in cells

Cells A3 and A4 contain text that "spills over the right edge of the cell". I wanted a formula which gives me this result:

Long text distributed over cells

Download the accompanying file JustifyText.xlsx (downloaded 121 times)

I needed a formula that splits the content of multiple cells into multiple rows. And I wanted to be able to set the maximum number of characters per row.

I used two separate Lambda formulas for this purpose:

LineSplitter

=LAMBDA(txtLen,inputTxt,
        LET(
            MaxLineLen, txtLen,
            txt, inputTxt & " ",
            spaceCt, LEN(txt) - LEN(SUBSTITUTE(txt, " ", "")),
            spaces, LEN(TEXTBEFORE(txt, " ", SEQUENCE(, spaceCt))),
            break, MAX(IF(spaces > MaxLineLen, -1, spaces),INDEX(spaces,1)),
            first, TRIM(LEFT(txt, break)),
            second, TRIM(SUBSTITUTE(txt, first, "")),
            IF(
               LEN(second) = 0,
               first,
               first & "|" & IF(LEN(second) > MaxLineLen, LineSplitter(MaxLineLen, second), second)
              )
            )
        )

The LineSplitter lambda adds a separator character after a maximum of the set # of characters we want, taking into account that we're not splitting in the middle of words. The result of LineSplitter on the content of cell A3 is:

"1. This long text goes into more than one cell,|there might be many rows needed".

The pipe character "|" indicates where we'll be splitting the sentence into rows. LineSplitter calls itself recursively, to insert all line splits.

Let me try to explain how this lambda works.

Now that we have inserted pipe characters everywhere where the text needs to spill to the next row, we need to build a vertical array of the bits of text in-between each set of pipes. That way we create a formula that will automatically spill over the cells as far as needed. This is done using the next lambda.

JustifyText

This is the lambda that we'll be using in the cell where we want our end-result. It will use the aforementioned LineSplitter lambda to place the pipe characters in the correct positions and then split the text up using the pipe characters. Finally, it stacks the array of pieces of text vertically:

= LAMBDA(maxLineLen, inputArray,
         TEXTSPLIT(
                   TEXTJOIN(
                            "|", TRUE,
                            MAP(
                                inputArray,
                                LAMBDA(
                                       b,
                                       LineSplitter(maxLineLen, b)
                                       )
                                )
                            )
                   , , "|", TRUE
                   )
        )

This function works like this:

 


Comments

Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.




To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].