Text replacement for partial values form a long string

[mdd16]

Hello

Need help for my most difficult code challenge one so far

I wish to create a code (or a formula) that replaces partial strings of cells in a range “Col_Orig_Text” as per pre-defined replacement values stored in a two column table named “repl_tab” and writes new values in a range named “Col_New_Text”.
- The twist comes when the Original string has several characters separated by space.
- Sometimes the partial original string will be one letter, sometimes two letters.
- The Original String will be parts of total string and part strings are separated by space.


repl_Tab = table with predefined relationship of two sets of Alphabets
repl_Tab, Col 1 = Original Text
repl_Tab, Col 2 = Replacement Text

Original string source: “Col_Orig_Text”
Original string : Col_New_Text”.


Example

Original Cell Content : B <Sapce> A <Sapce> A <Sapce> B <Sapce> E <Sapce> G <Sapce> E <Sapce> D <Sapce> D <Sapce> E
New Replacement cotent: : G# <Sapce> F#<Sapce> F# <Sapce> G# <Sapce> E <Sapce> B <Sapce> B <Sapce> C# <Sapce>

OR
Orignal text : B A A B E G E D D E
Converted text : G F# F# G C# E C# B B C#

 

[mdd16]

I solved it partially.... not very sophisticated .. but for now can live with it..

If someone can help do a VBA code will be far more elegant..

Col 1 : original text.
Col 2 to 20 : TextSplit Formula used to separate original text into multiple cells based on space as the text delimiter operator.
Col 20 to 38 : Used Index+Match functions to create replacement text, one cell per letter.
Col 39 : Used TextJoin formula to build back one common string.