CODEWITHSUNDEEP
excelexcel-formulasubstitution
darkzero901
darkzero901

Reputation: 21

Complex Substitute Beyond 64 Nesting Limit

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

Upvotes: 2

Views: 562

Answers (1)

JvdV
JvdV

Reputation: 75840

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,""))))

Upvotes: 2

Related Questions