2

I am using a nested SUBSTITUE function on several cells with long strings in them and update the SUBSTITUE fx regularly which results in me copying and pasting it to all the cells that need it. Problem is, my SUBSTITUTE list grows longer over time and I'm already at the max 64 levels of nesting.

Is there a way to create a new sheet that has one column of the word to search for and the second to replace it with, and somehow call that sheet as a SUBSTITUTE for each cell that needs it? That way I'm not limited by 64 levels of nesting, and I don't need to copy/paste the formula every time it changes for every cell that uses it.

Or some sort of other non-VBA/macro solution. Thanks

JvdV
  • 70,606
  • 8
  • 39
  • 70
darkzero901
  • 21
  • 2
  • 1
    Yes the function is called `REDUCE()` and is lambda-based. Excel insiders required at the moment. – JvdV Feb 02 '22 at 18:49
  • @JvdV Sir, i dont know whether it would be right to ask, could you share the function `REDUCE()` --> just for learning. – Mayukh Bhattacharya Feb 02 '22 at 19:31
  • 1
    @MayukhBhattacharya, as per your request, see the answer. Note how I also used it in [this](https://stackoverflow.com/a/65363987/9758194) Q&A. – JvdV Feb 02 '22 at 19:51
  • @JvdV Sir, much thanks for the same.! – Mayukh Bhattacharya Feb 02 '22 at 19:52
  • @JvdV Sir, i am using O365 Beta Channel, could you guide where i am going wrong, because it gives me `#NAME?` although i see the `LAMBDA` & `REDUCE` in the functions library. – Mayukh Bhattacharya Feb 02 '22 at 20:28
  • @JvdV Sir, it worked perfectly like a charm, amazing really, i have to type it manually again to make it work, `=TRIM(REDUCE(A1,C1:C15,LAMBDA(x,y,SUBSTITUTE(x,y,""))))` – Mayukh Bhattacharya Feb 02 '22 at 20:34
  • 1
    @makukhbhattacharya, not sure but for me the `MAP()` function isn't working though available in the menus. I guess it's just the beta-insiders side of things and it's bugged for some. – JvdV Feb 02 '22 at 21:01
  • @JvdV Sir, later it worked for me, when i typed the same manually, and i am sure there are two reasons why it wasnt working one might be because of some non printable characters because i copied from here and pasted the same in the cell A2 and other one is you need to edit the formula, may be because `SUBSTITUTE` Function is showing like this in the answers you have given `=TRIM(REDUCE(A1,C1:C15,LAMBDA(x,y,SUBSTITUEREN(x,y,""))))` – Mayukh Bhattacharya Feb 02 '22 at 21:05
  • 1
    I had the Dutch version non-translated. Thanks – JvdV Feb 02 '22 at 21:10
  • @JvdV Sir aha that's ok, i wasn't aware, sorry for the same. Thank you again for imparting so much valuable knowledge.!! – Mayukh Bhattacharya Feb 02 '22 at 21:12

1 Answers1

2

You can use REDUCE() as it's lambda-based and will iterate over all your substrings you would like to substitute:

enter image description here

Formula in A2:

=TRIM(REDUCE(A1,C1:C15,LAMBDA(x,y,SUBSTITUTE(x,y,""))))
JvdV
  • 70,606
  • 8
  • 39
  • 70