Not using VBA but just simple excel, can anyone help me find a solution to this problem? Would greatly appreciate it!
I have a list of Names in Sheet 1 like below
| - | A |
|---|---|
| 1 | sp_abc_Rick |
| 2 | sp_abc_Jabba_the |
| 3 | sp_abc_Dany |
| 4 | sp_random_Rick |
| 5 | sp_random_Jabba_the |
| 6 | sp_random_Dany |
| 7 | sp_constant |
| 8 | sp_ripley_art_Dany |
| 9 | sp_ripley_art_Jabba_the |
| 10 | sp_wakeup |
I have a list of Mapping Table in Sheet 2 like below
| - | A | B |
|---|---|---|
| 1 | Rick | Morty |
| 2 | Jabba_the | Hutt |
| 3 | Dany | Dragon |
I wish to have a result in Sheet 1, in column B, like below
| - | A | B |
|---|---|---|
| 1 | sp_abc_Rick | sp_abc_Morty |
| 2 | sp_abc_Jabba_the | sp_abc_Hutt |
| 3 | sp_abc_Dany | sp_abc_Dragon |
| 4 | sp_random_Rick | sp_random_Morty |
| 5 | sp_random_Jabba_the | sp_random_Hutt |
| 6 | sp_random_Dany | sp_random_Dragon |
| 7 | sp_constant | sp_constant |
| 8 | sp_ripley_art_Dany | sp_ripley_art_Dragon |
| 9 | sp_ripley_art_Jabba_the | sp_ripley_art_Hutt |
| 10 | sp_wakeup | sp_wakeup |
To give you a context of the number of rows. Sheet 1 will be bigger with more than 1000 rows. Sheet 2 (Mapping Table) is constant set of rows. Currently it is about 100 rows.
CodePudding user response：
You can use a formula like shown below using LOOKUP(), SEARCH() with SUBSTITUTE()
• Formula used in cell B1
=IFERROR(SUBSTITUTE(A1,LOOKUP(9^9,SEARCH($D$1:$D$3,A1),$D$1:$D$3),
LOOKUP(9^9,SEARCH($D$1:$D$3,A1),$E$1:$E$3)),A1)
CodePudding user response：
There you go. There may have other better solution. This is what I got.
All in column B.
=IFERROR(CONCAT(MID(A1,1,MATCH(1,(CODE(MID(A1,ROW($Z$1:$Z$255),1))<90)*(CODE(MID(A1,ROW($Z$1:$Z$255),1))>=65),FALSE)-1),INDIRECT(CONCAT("sheet2!b", MATCH(MID(A1, MATCH(1,(CODE(MID(A1,ROW($Z$1:$Z$255),1))<90)*(CODE(MID(A1,ROW($Z$1:$Z$255),1))>=65),FALSE), LEN(A1)), Sheet2!$A$1:Sheet2!$A$300, 0)))),A1)
Break down is as follow;
Let's start put things from Column C onward.
Column C, to find the index of the first capital letter from the text.
ref: 
Formula in B1:
=BYROW(A1:A10,LAMBDA(a,LET(b,TEXTBEFORE(a&"|","_"&A12:A14&"|",-1),IFERROR(CONCAT(IF(b&"_"&A12:A14=a,b&"_"&B12:B14,"")),a))))
The concatenation with a "|" would assert we only replace values when at the exact end of the input. Just in case there would be a stray (for example) 'Rick' somewhere before the end.