Forum Discussion

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Jun 05, 2023

    aliadm1490 

    This is just playing with 365.  I set out to remove multiple space-separated sub-strings listed as 'secondary' from the corresponding 'main'

     

    Worksheet formula
= MAP(main, secondary, Removeλ)

Removeλ
= LAMBDA(string, sub, REDUCE(string, TEXTSPLIT(sub, " "), SUBSTλ))

SUBSTλ
= LAMBDA(x, y, TRIM(SUBSTITUTE(x, y, "")))

     

     

     

    • PatrickvSvB's avatar
      PatrickvSvB
      Copper Contributor
      Feb 22, 2024

      Hi PeterBartholomew1, This sounds exactly like something I currently need. Could you explain how to insert this into a cell / table column? Thanks a lot!

       

      With kind regards, 

      Patrick

      • PeterBartholomew1's avatar
        PeterBartholomew1
        Silver Contributor
        Feb 22, 2024

        PatrickvSvB 

        To keep the worksheet as clear as possible, I used

        = MAP(Main, Secondary, Removeλ)

        as the worksheet formula.  I then used defined Names to refer to the two Ranges (a column of full texts strings and a column of strings to remove).  The Lambda functions Removeλ and SUBSTλ are also defined using Name Manager or the AFE (Advanced Formula Editor).

         

        If you prefer to minimise your use of Name Manager, an alternative exists of defining these LAMBDA functions within LET as a worksheet formula.

        = LET(
    Removeλ, LAMBDA(string,sub, REDUCE(string, TEXTSPLIT(sub, " "), SUBSTλ)),
    SUBSTλ, LAMBDA(x,y, TRIM(SUBSTITUTE(x, y, ""))),

    MAP(Main, Secondary, Removeλ)
  )

        If you are using Tables and Structured References, the references to the strings become

        [@Main] and [@Secondary] and Removeλ can be used directly without the MAP.

         

        You only need to make one of these work, but I do not know which you might prefer!

Resources