Forum Discussion

Lorenzo's avatar
Lorenzo
Silver Contributor
Nov 20, 2023

DrExcel_Excel_MVP 

Power Query for the record

// Table
let
    Source = Excel.CurrentWorkbook(){[Name="Table"]}[Content],
    Fixed = Table.ReplaceValue(Source, each [Product code], "0",
        (x,y,z)=> Text.Trim(y,z),
        {"Product code"}
    )
in
    Fixed

 

 

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Nov 20, 2023

Lorenzo 

(x,y,z)=> Text.Trim(y, z) shall work, isn't it?

  • 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
        Silver 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

Resources