0

I use a very long formula only with substitute before I touch the limit of 64 levels and somebody tell me I can use filterxml but how

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE((C3),"BON",""),"LOR",""),"GAN",""),"bon",""),"gan",""),"bon.",""),"lor.",""),"gan.",""),"ARZ","ARTE"),"ARZ.","ARTE"),"ARZ ","ARTE"),"arz","ARTE"),"arz.","ARTE"),"arz ","ARTE")

How can I do this?

JvdV
  • 70,606
  • 8
  • 39
  • 70
Ramo
  • 1
  • What is the goal here? `FILTERXML()` can **not** return reworked notes, so it can't specifically substitute values from a cell. It does have a 'translate()' function, but I'm unsure if that is helpfull. I'd rather opt for the new lambda helper function `REDUCE()` where you could feed an array to a recursive call on `SUBSTITUTE()`. Best is to show sample data with desired results to further explain your needs. – JvdV Mar 17 '22 at 08:15
  • [To give you an idea](https://stackoverflow.com/a/70961979/9758194) – JvdV Mar 17 '22 at 08:34
  • in my excel is no REDUCE. I look for another method because im tired using long long nested SUBSTITUTE. my goal is the same like in your example link, just without using columns – Ramo Mar 17 '22 at 14:14

0 Answers0