excellambdaexcel-formulaexcel-lambda

Recursive LAMBDA to replace characters by specific substitutes from a lookup table


The goal is to iterate through rows of the character table and replace each character with it's substitute.

The character table in this example is ={"&","&amp;";"<","&lt;";">","&gt;";"'","&apos;";"""","&quot;"}, or:
enter image description here
*(Sidenote: "&","&amp;" must be last on the list in this exact case, or it will replace other occurrences from previous substitutions, since we're going last to first.)

Formula:

=LAMBDA(XML,Pos,
LET(
Cls,{"&","&amp;";"<","&lt;";">","&gt;";"'","&apos;";"""","&quot;"},
Row,IF(ISOMITTED(Pos),ROWS(Cls),Pos),
Crf,INDEX(Cls,Row,1),
Crr,INDEX(Cls,Row,2),
Sub,SUBSTITUTE(XML,Crf,Crr),
IF(Row=0,XML,ENCODEXML(Sub,Row-1))
))

Expected result for =ENCODEXML("sl < dk & jf") would be sl &lt; dk &amp jf
I'm getting #VALUE! error instead.


Solution

    1. You need to have an exit on the recursive:
    =LAMBDA(XML,Pos,
    LET(
    Cls,{"&","&amp;";"<","&lt;";">","&gt;";"'","&apos;";"""","&quot;"},
    Row,IF(ISOMITTED(Pos),ROWS(Cls),Pos),
    Crf,INDEX(Cls,Row,1),
    Crr,INDEX(Cls,Row,2),
    Sub,SUBSTITUTE(XML,Crf,Crr),
    IF(Row>1,ENCODEXML(Sub,Row-1),Sub)
    ))
    
    1. You need to add the , in the call:
    =ENCODEXML("sl < dk & jf",)
    

    enter image description here

    Or as @Filcuk discovered(and I learned just now) if it is optional it needs to be declared using []

    ie:

    =LAMBDA(XML,[Pos],
    LET(
    Cls,{"&","&amp;";"<","&lt;";">","&gt;";"'","&apos;";"""","&quot;"},
    Row,IF(ISOMITTED(Pos),ROWS(Cls),Pos),
    Crf,INDEX(Cls,Row,1),
    Crr,INDEX(Cls,Row,2),
    Sub,SUBSTITUTE(XML,Crf,Crr),
    IF(Row>1,ENCODEXML(Sub,Row-1),Sub)
    ))
    

    Then the , is not needed:

    =ENCODEXML("sl < dk & jf")
    

    enter image description here