Search code examples
stringgoogle-sheetsreplace

Replace chars in string by using 2 columns as "dictionary"

I have a string in which I need to replace some characters. Let's say 1234AaCD must be replaced with 1Ą3Ęx•7D Now, because the rules what char gets replaced with what are complicated, I have 2 columns with chars. In column A I have source chars, and in B I have replacements:

A: source B: replacements
2 Ą
4 Ę
A x
a •
C 7

If char in string is not found in column A, leave it as it is. If char is found in column A, replace it with whatever is in column B (could be 1 char, could be a few, could be empty (so replace with ""). And I also need it case sensitive.

I also should mention that my "dictionary" is not just a few rows, like in the example, it's hundreds of rows, so I'd prefer to avoid nested RegexReplace. Also because we may need to add extra rows in the future, which would require very tedious work to fix the nested formula.

I've already found something similar here: Remove diacritics via formula on Google Sheets (Part 2) but it is not case sensitive (fakes that by just capitalizing each word as last step) and as I tried to make changes to that, it stopped working completely.

Solution

  • This problem can be nicely solved using the REDUCE function

    =REDUCE(A10,SEQUENCE(5),LAMBDA(str,i,SUBSTITUTE(str,INDEX(A2:A6,i),INDEX(B2:B6,i))))

    enter image description here

    This formula is equivalent to:

    =SUBSTITUTE(
   SUBSTITUTE(
     SUBSTITUTE(
       SUBSTITUTE(
         SUBSTITUTE(A10,A2,B2),
         A3,B3),
       A4,B4),
     A5,B5),
   A6,B6)

    Edit - If you want to make it work with open ranges, replace 5 with COUNTA(A2:A) and drop the end row of the ranges.

    =REDUCE(D3,SEQUENCE(COUNTA(A2:A)),LAMBDA(str,i,SUBSTITUTE(str,INDEX(A2:A,i),INDEX(B2:B,i))))

    enter image description here