Excel LAMBDA function, Examples

Content

• Introduction
• Name of worksheet in a cell
• Justifying text

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:

 

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 120 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:

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

Download the accompanying file JustifyText.xlsx (downloaded 139 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.

• The lambda takes two arguments: txtLen and inputTxt. I believe those are self-explanatory.
• The LET function.
  • MaxLineLen: Simply relabels the first argument txtLen
  • txt: simply inputTxt with an appended space. This avoids errors when a cell contains a single word.
  • spaceCt: counts the number of spaces in our text
  • spaces: returns an array of the positions of each space character in our text.
  • break: Calculates where our first line break must appear. Because the total text length might be less than the target line length the minimum break position is the length of the first word in our text, calculated by "INDEX(spaces,1)"
  • first: the part of our text, up to the first break
  • second: the remaining text to be broken up
  • The IF function decides whether or not we must do another recursion into LineSplitter, to split up any remaining text (checks of second is empty, if not calls LineSplitter with just the remaining piece of text.

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:

• maxLineLen, inputArray: the maximum length of the line and the array of cells we want split up across rows.
• Then we use the MAP function to create a new array by running its elements through the LineSplitter lambda, once for each item in inputArray. This gives us two strings:
  • 1. This long text goes into more than one cell,|there might be many rows needed
  • 2. This is another cell with too much text, it|needs to spill below the previous cell's spills
• The TEXTJOIN function then creates one larger string, combining the strings from the previous step, separated by the pipe character:
  • 1. This long text goes into more than one cell,|there might be many rows needed|2. This is another cell with too much text, it|needs to spill below the previous cell's spills
• Finally, the TEXTSPLIT function creates a vertical array of the shortened strings, by splitting up the string from the previous step using the pipe character.