8

At risk of being off-topic I decided to do a little Q&A as I'm pretty excited about a new function MS is introducing to Excel365; the LAMBDA() function. If the general opinion is such that this is off-topic, please let me know and I can take down the Q&A.

The LAMBDA() function is basically your way in Excel itself to create your own function. You then can go ahead and call this function throughout your entire workbook. But the absolute great thing (IMHO) about it is that it is able to call itself within the function, thus being recursive!

We all know the tedious nested SUBSTITUTE() functions if one has to swap multiple characters, or clear a string from certain characters and even whole words. So the question is: How do we avoid that and use LAMBDA() to our advantage?

ZygD
  • 22,092
  • 39
  • 79
  • 102
JvdV
  • 70,606
  • 8
  • 39
  • 70

1 Answers1

11

EDIT 22-3-2022:

As per the new functionality, one can choose to opt for:

=CONCAT(TEXTSPLIT(A1,{"+","#","%","*","(",")","!"}))

I'll keep the original answer using LAMBDA() intact below:

Original Answer:

So let's create an example of a string that needs cleaning; a+b#c%d*e(f)g!h.

enter image description here

Formula in B1:

=SUBALL(A1,"+#%*()!","")

Where SUBALL() is the name of our LAMBDA() function I created through the "name manager" menu and reads as follows:

=LAMBDA(str,chrs,sub,IF(chrs="",str,SUBALL(SUBSTITUTE(str,LEFT(chrs),sub),RIGHT(chrs,LEN(chrs)-1),"")))

Core of this formula are the 3 variables:

  • str - A reference to the string to be cleaned.
  • chrs - A string of characters to be substituted.
  • sub - What do we want our characters to be replaced with?

The 4th parameter is a nested IF(). Because of the recursive calls we need a way out of an otherwise infinite loop. Therefor we test if chrs="". If TRUE we return the final string with all substituted characters. If FALSE we call the function again. The great thing here is we can alter all variables! This is important because we can thus SUBSTITUTE() the leftmost character and we can cut that same character of the string of replacements.

We could also take it up a notch and replace element from an array. For example:

enter image description here

The formula in B1:

=SUBALL(A1,{"STR1","STR2","STR3"},"-")

Note, you can also hardcode a single value or reference a single cell (or any vertical range for that matter). Obviously this will impact the way we handle recursion. There may be a prettier way, but I came up with:

=LAMBDA(str,del,sub,IF(COUNTA(del)=1,SUBSTITUTE(str,@del,sub),SUBALL(SUBSTITUTE(str,@del,sub),INDEX(del,SEQUENCE(COUNTA(del)-1,,2)),sub)))

The core of the function is still the same, but as mentioned we have now used an array. So our IF() will no longer check for empty value, but if there is only a single element in our array. If so, it will go do a single SUBSTITUTE(), but if not, then it will recursively call SUBALL() untill we have sliced enough values from our array through INDEX() so all values are substituted.

There you have it, a recursive function that you can now call throughout your entire workbook. Pretty cool.

JvdV
  • 70,606
  • 8
  • 39
  • 70
  • Very useful but the only problem is going to be when you write for someone else and they aren't using Office365 – TobyPython Dec 19 '20 at 00:03
  • 1
    @TobyPython, that's a problem with all version-based functions unfortunately. – JvdV Dec 19 '20 at 10:55
  • what's the meaning of `@del` - why the @ symbol? – Greedo Dec 21 '20 at 17:01
  • Also I posted a [question](https://codereview.stackexchange.com/q/253549/146810) on recursive Lambdas on code review, maybe post yours there if you are looking for another way of doing it - for example taking a range and using `Offset` to iterate over it may be a cleaner approach, I'm not sure – Greedo Dec 21 '20 at 17:06
  • 1
    @Greedo, the thing is I knew about `OFFSET()` but since that is volatile I don't know if it would make the `LAMBDA()` volatile too. The `@` makes that we do not use an entire array but make sure we take the first element from our del array (or string). Without it, the `LAMBDA()` messes up. Hope that helps. Thanks for your interest in the function =) – JvdV Dec 21 '20 at 20:33