Excel Replace Text via Another Ranges

Question

In column A i have a long text, containing substrings that you can find on another range (column D). I want to search for the substring and if I find it then replace it with the value in column E.

Any help will be much appreciated.

Also you can refer this solution posted by @JvdV Sir : **[Here](https://stackoverflow.com/questions/70959273/complex-substitute-beyond-64-nesting-limit)** on multiple substituting. — Mayukh Bhattacharya, Jun 19 '23 at 12:42

Ike · Answer 1 · 2023-06-20T07:44:47.877

1

If you have Excel 365 you can use this formula in column B:

=LET(mapping, tblMapping,
REDUCE([@text],INDEX(mapping,,1)
        LAMBDA(a,b,SUBSTITUTE(a,b,XLOOKUP(b,INDEX(mapping,,1),INDEX(mapping,,2))))))

This will also work if there are two or more relevant texts - s. last row

Don't forget to apply "wrap text" to column B.

EDIT according to question in comments: You can use this formula to return true / false depending on wether at least one text has been found:

=REDUCE(FALSE,tblMapping[Text to search],
            LAMBDA(a,b,IF(a=TRUE,a,IFERROR(FIND(b,[@text])>0,FALSE))))

edited Jun 20 '23 at 07:44

answered Jun 19 '23 at 12:22

Ike

9,580
4
13
29

1

While I'm a big fan of LET, maybe it's easier to read avoiding LET in this case, since the table headers are self explaining: `=REDUCE([@Text],ListB[Text to search],LAMBDA(x,y,SUBSTITUTE(x,y,XLOOKUP(y,ListB[Text to search],ListB[Replace with]))))`. Good solution in case of multiple possible strings to replace in the same text. – P.b Jun 19 '23 at 20:00
@P.b: Good point. – Ike Jun 19 '23 at 20:17
it works pefectly P.b. How would you write a formula in another column that just returns TRUE/FALSE if cell in column 'Text' contains one of the substrings in column 'Text to search'? – Kobe2424 Jun 19 '23 at 21:37
I added the solution to return true/false – Ike Jun 20 '23 at 07:45

Mayukh Bhattacharya · Answer 2 · 2023-06-19T12:56:37.063

You could try using the following formula, which is based on your data presented on the screenshot above.

• Formula used in cell B3

=LET(
     a,[@Text],
     b,FILTER(Table2,ISNUMBER(FIND(Table2[Text to search],a))),
     c,INDEX(b,,1),
     d,INDEX(b,,2),
     IFERROR(SUBSTITUTE(a,c,d),a))

Since in one of the cells you are using line-feed, hence please remember to use wrap text, by hitting ALT+H+W for the desired result column.

Excel Replace Text via Another Ranges

2 Answers2