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:
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 81 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 100 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.
