Replace Substrings In Excel

Question

I want to change substrings in excel based on a pre-defined value stored in a table.

How can I do that in Excel 2019 without VBA?

Example:

table1

KEY	TEXT
01	ABCDEFGHIJ
02	KHAZENCKAL
03	PZTEWRNBGM

now I have another table with the stored values to be used.

table2

OLD VALUE	NEW VALUE
A	AYY
B	BEE
C	CEE
D	DEE
E	EEE
F	FEE

..and so forth

now I want to add a new column to table1 that contains that new values.

table1 (updated)

KEY	TEXT	NEW TEXT
01	ABCDEFGHIJ	AYYBEECEEDEEEEEEFFGEEAYHEYEJAY
02	KHAZENCKAL	KAYAYHAYYZEEEEEENNCEEKAYAYYELL
03	PZTEWRNBGM	PEEZEETEEEEEWYUAREENNBEEGEEEMM

How can I achieve that?

Appreciate the help.

edit1: formula not working

Answer 1

Here is one way you could try using TEXTJOIN() & VLOOKUP()

---

=TEXTJOIN("",1,
 IFNA(VLOOKUP(
 MID([@TEXT],
 ROW($ZZ$1:INDEX($Z:$Z,LEN(B2))),1),Table2,2,0),""))

---

Or other way which is not suggestive to use is INDIRECT() function to create the sequence:

=TEXTJOIN("",1,
 IFNA(VLOOKUP(
 MID([@TEXT],
 ROW(INDIRECT("1:"&LEN(B2))),1),Table2,2,0),""))

---