Google Sheets – More compact version of the nested replacement function possible?

I want to make about a hundred changes to a column in a table. These changes must be made each time a new copy of the original is made. The changes are spread over several thousand lines.

The first solution that came to my mind was to use VLookup.
Bad column in A, good column in B.
Start by copying from A to B. Correct the entries in B.
This requires several bugs to be fixed. Ok, search and replace in a limited area.

That works, but it's not very robust.

When I repaired Eleagnus for Elaeagnus
Eleagnus commutata and for Eleagnus augustifolia I have to fix it again if my supplier Eleagnus & # 39; Silverleaf & # 39; introduces

I have found a partial solution to a variety of changes in a column problem.

Enter image description here

When I use the yellow formula, the yellow changes are implemented.
Nest them with B10, C10 – the pink formula – adds the changes in pink.
Nest The Another replacement () adds the changes in green.

It will be unwieldy.

Currently my solution is messy and requires at least one new tab. Suppose the neat stuff starts on line 4 and we use the first 3 for labels and explanations.

  • Copy the column from the source into column A and use a matrix formula, e.g.

A4 = ArrayFormula (SheetX! C4: C)

  • Build the replacement expressions in columns B and C.
  • Put the messy replacement array formula in D4
  • If N is the number of substitutions in my formula, columns E, F are copies of BC, but start N rows below. For N = 10

E = ArrayFormula (B14: B) F = ArrayFormula (C14: C)

  • Copy D4, E4, F4 to G4, H4, I4
  • Repeat until the print list is exhausted.

Copy the last column to the desired location.


Is there an alternative to nested replacements in Google Sheets?

Basically I want to:

FOR $ counter = 4 to N
ArrayFormula (replacement (A4: A, B $ counter, C $ counter))
End for

A fairly simple solution is to do it in Perl. That doesn't make my solution portable. Few of my people are Perl users.

Is there an elegant way to do this in arches?