4

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.

ZygD
  • 22,092
  • 39
  • 79
  • 102
Filcuk
  • 154
  • 18
  • 1
    see the bottom part of my answer here: https://stackoverflow.com/questions/68685282/can-you-use-substitute-for-many-values-without-nesting This will do what you want using scan and Lambda. – Scott Craner Apr 27 '22 at 14:40
  • @ScottCraner you're absolutely right, I must admit I could use UDFs too, but I was hoping to learn why my formula doesn't work. I don't suppose you'd know what the issue is? It doesn't seem to like the way I handle `Pos` & `Row`, as when I make `Pos` a required argument and manually enter the number of iterations (5 in this case), it works just fine. – Filcuk Apr 27 '22 at 14:48
  • 1
    At the bottom of my answer is a formula only version. I answered that before Lambda and Scan were readily available. I have not been able to make the name manager versions of Lamba work consistently and since scan and reduce were introduced I do not bother anymore trying. – Scott Craner Apr 27 '22 at 14:51
  • 2
    @Filcuk try this: `=ENCODEXML("sl < dk & jf",)` – Scott Craner Apr 27 '22 at 14:54
  • 1
    @ScottCraner blast it, that's it! You're a legend Scott. – Filcuk Apr 27 '22 at 14:55
  • 6
    Instead try: `=LET(X,{"&","&";"<","<";">",">";"'","'";"""","""},REDUCE("sl < dk & jf",INDEX(X,0,1),LAMBDA(a,b,SUBSTITUTE(a,b,VLOOKUP(b,X,2,0)))))` and safe yourself the hassle of using the name manager. – JvdV Apr 27 '22 at 15:03
  • 1
    @JvdV, will you go to your answer on the linked question and add the version using Reduce? That way we can have a cannon question to point people to. This comes up quite often. – Scott Craner Apr 27 '22 at 15:09
  • 1
    @ScottCraner, due to the nature of that question it does not seem to be fitting to talk about a nested `VLOOKUP()` or using `LET()`. I edited the answer though. – JvdV Apr 27 '22 at 15:19
  • 1
    @JvdV, I would agree, I kind of went overboard on the replace part, giving options to replace with other things beside all the same. I read my answer and not the question and figured that your formula would be a good fit, again assuming that the user wants to replace each with something different and not the same. – Scott Craner Apr 27 '22 at 15:27

2 Answers2

7
  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

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • 2
    For anyone else reading this, I wasn't aware that optional parameters must be declared using square brackets, so `=LAMBDA(XML,[Pos])` would work as `=ENCODEXML("your text")` without having to add the comma – Filcuk Apr 27 '22 at 15:02
6

Just to complement the answer above by Scott; using a recursive lambda through the name manager seems to be obsolete (if one doesn't explicitly need a named function for later use). Since REDUCE() is a recursive function on it's own. Therefor, one can apply the following structure:

=LET(X,<LookupTable>,REDUCE(<InputValue>,INDEX(X,0,1),LAMBDA(a,b,SUBSTITUTE(a,b,VLOOKUP(b,X,<ReturnCol>,0)))))

Where:

  • <LookupTable> - Refers to a matrix where the leftmost column holds the lookup values. This is particularly true for VLOOKUP() however, with different structures one can start using XLOOKUP() (to make the solution more applicable);
  • <InputValue> - A reference to the input string you need to apply the substitution to;
  • <ReturnCol> - In addition to the 1st point: when one uses VLOOKUP() an index refering to the column with the replacement values need to be given;

In the case given by OP this would translate to:

=LET(X,{"&","&amp;";"<","&lt;";">","&gt;";"'","&apos;";"""","&quot;"},REDUCE("sl < dk & jf",INDEX(X,0,1),LAMBDA(a,b,SUBSTITUTE(a,b,VLOOKUP(b,X,2,0)))))
JvdV
  • 70,606
  • 8
  • 39
  • 70