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 ={"&","&";"<","<";">",">";"'","'";"""","""}
, or:
*(Sidenote: "&","&"
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,{"&","&";"<","<";">",">";"'","'";"""","""},
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 < dk & jf
I'm getting #VALUE!
error instead.
=LAMBDA(XML,Pos,
LET(
Cls,{"&","&";"<","<";">",">";"'","'";"""","""},
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)
))
,
in the call:=ENCODEXML("sl < dk & jf",)
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,{"&","&";"<","<";">",">";"'","'";"""","""},
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")