Search code examples
google-sheetsgoogle-sheets-formula

Formula to find&replace part of a string

Column A contains the string you want to edit.
Column C contains a string to find some word in the string in column A.
Column D contains the string that replaces that string.
Results are returned in column F.

This is the result I want.

a b c d e f
result
its test. apple apple A its test. A
its test. banana banana B its test. B
apple & banana ... ... A & B
banana & banana B & B
its test. apple its test. A
its test. banana its test. B



I used this formula.
=ARRAYFORMULA(VLOOKUP(A2:A, C2:D, 2,TRUE))
and i got this wrong.

a b c d e f
result
its test. apple apple A B
its test. banana banana B B
apple & banana ... ... A
banana & banana B
its test. apple B
its test. banana B

I want to return only some of the strings have changed. If there are several texts to change in one string, I want to change them all.

Solution

  • You may try:

    =REDUCE(A:A,C:C,LAMBDA(a,c,IF(c="",a, INDEX (REGEXREPLACE(a,c,OFFSET(c,0,1))))))

    It's a basic REGEXREPLACE, but with REDUCE lets you scan the different words to be replaced

    enter image description here

    With Substitute:

    =REDUCE(A:A,C:C,LAMBDA(a,c,IF(c="",a, INDEX (SUBSTITUTE(a,c,OFFSET(c,0,1))))))