Excel replace each char in a string contained in another list-CodePudding

I have 2 tables, table A and table B, on column G I would like to encode only the Hebrew and spaces, but using the ENCODEURL encodes the whole string. I found a workaround and I wrote in Table A each of the chars I'm trying to replace and the encoded value. I want to search the string in column F for any of the chars that are in column B and replace (or substitute) them in the values in column C.

any help will be much appreciated.

Data in my image as text:

description DECODED ENCODED
letter  א   א
letter  ב   ב
letter  ג   ג
letter  ד   ד
letter  ה   ה
letter  ו   ו
letter  ז   ז
letter  ח   ח
letter  ט   ט
letter  י   י
letter  כ   כ
letter  ל   ל
letter  מ   מ
letter  נ   נ
letter  ס   ס
letter  ע   ע
letter  פ   פ
letter  צ   צ
letter  ק   ק
letter  ר   ר
letter  ש   ש
letter  ת   ת
letter  ן   ן
letter  ם   ם
letter  ץ   ץ
letter  ף   ף
letter  ך   ך
space        


Link
test/ם/

Desired result should be: test/ם/ and not test/מ/
(D in the end not E)

CodePudding user response：

With Microsoft365, try:

Formula in F2:

=LET(X,MID(E2,SEQUENCE(LEN(E2)),1),CONCAT(IFERROR(INDEX(C$2:C$24,MATCH(X,B$2:B$24,0)),X)))

I tried to swap the IFERROR(), INDEX() and MATCH() part with XLOOKUP() but it didn't work since the latter won't take an array as a lookup-value.

Right, after your updated question I think you could use:

=LET(X,MID(E2,SEQUENCE(LEN(E2)),1),CONCAT(IF(ISNUMBER(MATCH(X,B$2:B$29,0)),ENCODEURL(X),X)))

Now you can drop the 1st and 3rd column alltogether too.