Forum Discussion

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Nov 20, 2023

Lorenzo 

I'm sure lambda equivalent was done by someone, here is quickly done variant

TextTrim =
LAMBDA(txt, [ChrToTrim],
    LET(
        chr, IF(ISOMITTED(ChrToTrim), " ", ChrToTrim),
        IF( LEFT(txt)=chr,
            TextTrim( RIGHT( txt, LEN(txt)-1), chr),
            IF( RIGHT(txt) <> chr,
                IF( txt = "", "", txt),
                TextTrim( LEFT( txt, LEN(txt)-1), chr) )
)) )
djclements's avatar
djclements
Bronze Contributor
Nov 21, 2023

SergeiBaklan and DrExcel_Excel_MVP here's another recursive lambda method that uses fixed-point combinator logic to define and use the custom function directly in a cell:

 

=LET(
F, LAMBDA(X,s,c, LET(
   a, IF(LEFT(s)=c, RIGHT(s, LEN(s)-1), s),
   b, IF(RIGHT(a)=c, LEFT(a, LEN(a)-1), a),
   IF(OR(LEFT(b)=c, RIGHT(b)=c), X(X, b, c), b))),
F(F, B6:B14, "0"))

 

...where s is the string, cell or range of values to be cleaned, and c is the character to be removed (leading/trailing).

 

Example: In-Cell Recursive Lambda Function

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Nov 21, 2023

    djclements 

    The only it returns zero for the blank cell, better empty string

    • djclements's avatar
      djclements
      Bronze Contributor
      Nov 21, 2023

      SergeiBaklan It will only return a zero when used to evaluate a single blank cell, which kinda defeats the purpose of the function, so it's not really a concern. When using it on a range, however, if any of the cells within the range are blank, it returns an empty string for those cells. It also returns an empty string for any cells that contain all zeros (ie: "0000").

       

      The main benefits of this method include the following:

      • it can accept a range of values and spill the results, without using BYROW
      • there is no need to create the custom lambda function in Name Manager, if you only need to use it once within the workbook
      • PeterBartholomew1's avatar
        PeterBartholomew1
        Silver Contributor
        Nov 21, 2023

        djclements 

        Since you have raised the bar, the following function

        = LET(
    nonzero, Explodeλ(text) <> "0",
    first,   XMATCH(TRUE, nonzero),
    last,    XMATCH(TRUE, nonzero, , -1),
    IF(ISNUMBER(first), MID(text, first, 1 + last - first), "")
  )

         returns a null string for blank members of the list or for any that comprises nothing but 0s.  If MAP is a problem it can be hidden within a further LAMBDA function.

         

         

Resources