Forum Discussion

Lorenzo's avatar
Lorenzo
Silver Contributor
Nov 20, 2023

Good point SergeiBaklan + Thanks. Previous post revised

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
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Nov 20, 2023

    Or

    TextTrim =
LAMBDA(txt, [ChrToTrim],
    LET(
        chr, IF(ISOMITTED(ChrToTrim), " ", ChrToTrim),
        IF( LEFT(txt) = chr,
            TextTrim( REPLACE(txt,1,1,""), chr),
            IF( RIGHT(txt) <> chr,
                IF( txt = "", "", txt),
                TextTrim( REPLACE(txt,LEN(txt),1,""), chr) )
)) )

Resources