Numerous Methods to Accomplish a Task - Excel, M Code and Python
Task:
The strings given in column A have some alphabets which are masked i.e. replaced by asterisks.
The alphabets corresponding to those asterisks are given in column B.
Replace asterisks by corresponding characters of column B.
Hence, if string is "Go* is **eat" and chars is "dGr", then answer would be "God is Great".
Workbook link:
Solution:
Excel:
=MAP(A2:A10,B2:B10,LAMBDA(x,y,TEXTJOIN(MID(y,SEQUENCE(LEN(y)),1),0,TEXTSPLIT(x,,"*"))))
=MAP(A2:A10,B2:B10,LAMBDA(x,y,REDUCE(x,MID(y,SEQUENCE(LEN(y)),1),LAMBDA(a,v,SUBSTITUTE(a,"*",v,1)))))
Power Query:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
fx = (ms as text, ch as text) as text => if ch = "" then ms else @fx( Text.ReplaceRange(ms, Text.PositionOf(ms, "*",0),1,Text.Start(ch,1)), Text.Middle(ch,1)),
Result = Table.AddColumn(Source,"Result", each fx([Masked String],[Chars]))
in
Result
Python in Excel:
from functools import reduce
df = xl("A1:B10", headers=True)
S = df["Masked String"].values
C = df["Chars"].values
df["Result"] = [reduce(lambda x,y: x.replace("*",y,1),c,i) for i,c in zip(S,C)]
df
Comments