Empresas
Empleos
  • Sobre nosotros
  • Soluciones
    • Publicación de vacantes
      Publica tu vacante y recibe candidatos calificados en 48h.
    • Evaluación de candidatos
      500+ pruebas técnicas y psicológicas, más anti-fraude.
    • Headhunting
      Búsqueda ejecutiva a la medida de principio a fin.
    • Nómina + EOR
      Dispersión de nómina y EOR en más de 15 países de LATAM.
  • Precios
  • Empleos

0

221
Vistas
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.

over 4 years ago · Santiago Trujillo
2 Respuestas
Responde la pregunta

0

  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

over 4 years ago · Santiago Trujillo Denunciar

0

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)))))
over 4 years ago · Santiago Trujillo Denunciar
Responde la pregunta
Encuentra empleos remotos

¡Descubre la nueva forma de encontrar empleo!

Top de empleos
Top categorías de empleo
Empresas
Publicar vacante Precios Comercial
Legal
Términos y condiciones Política de privacidad
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Recomiéndame algunas ofertas
Necesito ayuda