excelexcel-formulaexcel-2019

Replace Substrings In Excel

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 enter image description here

enter image description here

Solution

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

    enter image description here

    =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),""))