Replace Masked Characters with Text Manipulation

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”.

📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 131
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Replace Masked Characters with Power Query

Power Query solution 1 for Replace Masked Characters, proposed by Bo Rydobon 🇹🇭:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Rs = Table.AddColumn(
    Source, 
    "Ans", 
    each 
      let
        t = Text.Split([Masked String], "*"), 
        c = Text.ToList([Chars] & " ")
      in
        Text.Trim(Text.Combine(List.Transform(List.Positions(t), (n) => t{n} & c{n})))
  )
in
  Rs

Power Query solution 2 for Replace Masked Characters, proposed by Bo Rydobon 🇹🇭:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Rs = Table.AddColumn(
    Source, 
    "Ans", 
    each Combiner.CombineTextByEachDelimiter(Text.ToList([Chars]), QuoteStyle.None)(
      Text.Split([Masked String], "*")
    )
  )
in
  Rs

Power Query solution 3 for Replace Masked Characters, proposed by Zoran Milokanović:

let
  Source = Excel.CurrentWorkbook(){[Name = "MaskedAlphabets"]}[Content], 
  Intermediate = Table.AddColumn(Source, "Temp", each Text.PositionOf([Masked String], "*", 2)), 
  ExpectedAnswer = Table.AddColumn(
    Intermediate, 
    "Expected Answer", 
    (o) =>
      List.Last(
        List.Generate(
          () => [
            i            = 0, 
            MaskedString = Text.ReplaceRange(o[Masked String], o[Temp]{0}, 1, Text.At(o[Chars], 0))
          ], 
          each [i] < List.Count(o[Temp]), 
          each [
            i = [i] + 1, 
            MaskedString = Text.ReplaceRange(
              [MaskedString], 
              o[Temp]{[i] + 1}, 
              1, 
              Text.At(o[Chars], [i] + 1)
            )
          ], 
          each [MaskedString]
        )
      )
  ), 
  Solution = ExpectedAnswer[[Expected Answer]]
in
  Solution

Power Query solution 4 for Replace Masked Characters, proposed by Aditya Kumar Darak 🇮🇳:

let
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  Output = Table.AddColumn(
    Source, 
    "Output", 
    each [
      Position = Text.PositionOf([Masked String], "*", Occurrence.All), 
      AllChar = Text.ToList([Chars]), 
      Zipped = List.Zip({Position, AllChar}), 
      Return = List.Accumulate(
        Zipped, 
        [Masked String], 
        (a, b) => Text.ReplaceRange(a, b{0}, 1, b{1})
      )
    ][Return]
  )
in
  Output

Power Query solution 5 for Replace Masked Characters, proposed by Alejandro Simón 🇵🇦 🇪🇸:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Sol = Table.AddColumn(
    Source, 
    "Answer", 
    each 
      let
        a = Text.Split([Masked String], "*"), 
        b = Text.ToList([Chars]) & {""}, 
        c = Text.Combine(List.Transform({0 .. List.Count(a) - 1}, each a{_} & b{_}), "")
      in
        c
  )[[Answer]]
in
  Sol

Power Query solution 6 for Replace Masked Characters, proposed by Venkata Rajesh:

let
  Source = Data, 
  Output = Table.AddColumn(
    Source, 
    "Result", 
    each 
      let
        _positions = Text.PositionOf([Masked String], "*", Occurrence.All), 
        _list      = Text.ToList([Chars]), 
        _Count     = {0 .. Text.Length([Chars]) - 1}
      in
        List.Accumulate(
          _Count, 
          Text.Remove([Masked String], {"*"}), 
          (state, current) => Text.Insert(state, _positions{current}, _list{current})
        )
  )
in
  Output

Power Query solution 7 for Replace Masked Characters, proposed by Jan Willem Van Holst:

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "PY9BagMxDEWvIrwUucRQSAttIbQpXYRZeGw1VmNskO2Ad71Gr9eTVOMM8cILvfe/5dPJHDM8c4xgAbO7cMKFxZudeT2beac4EDwi2ao34tKViK1lMIy96FBHH4XKXX+w6AIJcAKl8NZJjVoD3ULCngCTBxT6bp7dWnCwhw37K6bahArkL3hq7hJpIZG+dq3mRHtOabhTZEd/P78FJmUjtT2qJ9q0fsNf6TMnT3JrR9A94IXPoYat8JhzK3TffoRBw8PcnPfckt836Wae/wE=", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [#"Masked String" = _t, Chars = _t]
  ), 
  #"Added Custom" = Table.AddColumn(
    Source, 
    "answer", 
    (row) =>
      List.Accumulate(
        {0 .. Text.Length(row[Chars]) - 1}, 
        row[Masked String], 
        (s, c) => Text.ReplaceRange(s, Text.PositionOf(s, "*"), 1, Text.Range(row[Chars], c, 1))
      )
  )
in
  #"Added Custom"

Solving the challenge of Replace Masked Characters with Excel

Excel solution 1 for Replace Masked Characters, proposed by Bo Rydobon 🇹🇭:

=MAP(A2:A10,B2:B10,LAMBDA(a,b,TEXTJOIN(MID(b,SEQUENCE(20),1),0,TEXTSPLIT(a,"*"))))

Excel solution 2 for Replace Masked Characters, proposed by Bo Rydobon 🇹🇭:

=REDUCE(A2:A10,SEQUENCE(20),LAMBDA(a,n,SUBSTITUTE(a,"*",MID(B2:B10,n,1),1)))

Excel solution 3 for Replace Masked Characters, proposed by Rick Rothstein:

=REDUCE(A2:A20,SEQUENCE(COUNTA(TEXTSPLIT(A2:A20,"*"))-1),LAMBDA(a,x,SUBSTITUTE(a,"*",MID(B2:B20,x,1),1)))

Excel solution 4 for Replace Masked Characters, proposed by John V.:

=MAP(A2:A10,B2:B10,LAMBDA(a,b,REDUCE(a,SEQUENCE(LEN(b)),LAMBDA(i,x,SUBSTITUTE(i,"*",MID(b,x,1),1)))))
✅=REDUCE(A2:A10,ROW(1:9),LAMBDA(i,x,SUBSTITUTE(i,"*",MID(B2:B10,x,1),1)))

Excel solution 5 for Replace Masked Characters, proposed by محمد حلمي:

=MAP(A2:A10,B2:B10,LAMBDA(a,b,
REDUCE(a,MID(b,SEQUENCE(LEN(b)),1),LAMBDA(a,d,
SUBSTITUTE(a,"*",d,SEQUENCE(LEN(d)))))))

Excel solution 6 for Replace Masked Characters, proposed by 🇰🇷 Taeyong Shin:

=REDUCE(A2:A10, SEQUENCE(MAX(LEN(B2:B10))), LAMBDA(a,n,
 SUBSTITUTE(a, "*", MID(B2:B10, n, 1), 1)
))


=REDUCE("", SEQUENCE( , MAX(LEN(A2:A10))), LAMBDA(a,n,
 LET(
 chr, MID(A2:A10, n, 1),
 txt, MID(A2:A10, 1, n),
 a & IF(chr = "*", MID(B2:B10, LEN(txt) - LEN(SUBSTITUTE(txt, "*", )), 1), chr)
 )
))

Excel solution 7 for Replace Masked Characters, proposed by 🇰🇷 Taeyong Shin:

=LET(R,LAMBDA(R,a,b,IF(AND(b=""),a,R(R,SUBSTITUTE(a,"*",LEFT(b),1),MID(b,2,LEN(b))))),R(R,A2:A10,B2:B10))

Excel solution 8 for Replace Masked Characters, proposed by Kris Jaganah:

=MAP(A2:A10,B2:B10,LAMBDA(a,b,(LET(c,SEQUENCE(LEN(a)),d,MID(a,c,1),e,MID(b,SEQUENCE(LEN(b)),1),g,FILTER(c,d="*"),CONCAT(IFNA(XLOOKUP(c,g,e),d))))))

Excel solution 9 for Replace Masked Characters, proposed by Julian Poeltl:

=MAP(A2:A10,B2:B10,LAMBDA(S,C,LET(SP,MID(S,SEQUENCE(LEN(S)),1),RP,IF(SP="*",MID(C,SCAN(0,SP,LAMBDA(A,B,IF(B="*",A+1,A))),1)),CONCAT(IF(RP=FALSE,SP,RP)))))

Excel solution 10 for Replace Masked Characters, proposed by Aditya Kumar Darak 🇮🇳:

=LET(
 _ms, A2:A10,
 _ch, B2:B10,
 _mx, MAX(LEN(B2:B10)),
 _e, LAMBDA(a, b, SUBSTITUTE(a, "*", MID(_ch, b, 1), 1)),
 _r, REDUCE(_ms, SEQUENCE(_mx), _e),
 _r
)

Excel solution 11 for Replace Masked Characters, proposed by Timothée BLIOT:

=LET(A, TEXTSPLIT(TEXTJOIN("/",,A2:A10),"*","/",,,""),
B, TEXTSPLIT(TEXTJOIN("/",," *"&MAP(B2:B10, LAMBDA(x, TEXTJOIN("*",,MID(x,SEQUENCE(LEN(x)),1)) ))),"*","/",,,""),
C, MAP(A,B, LAMBDA(a,b, CONCAT(b,a) )), BYROW(C, LAMBDA(x, TRIM(CONCAT(x)))))

Excel solution 12 for Replace Masked Characters, proposed by Sunny Baggu:

=MAP(A2:A10,B2:B10,LAMBDA(a,b,
LET(_TS,TEXTSPLIT(a,,"*"),
_chs,MID(b,SEQUENCE(LEN(b)),1),
CONCAT(TOCOL(HSTACK(_TS,_chs),3)))))

Excel solution 13 for Replace Masked Characters, proposed by Bhavya Gupta:

=MAP(A2:A10,B2:B10&" ",LAMBDA(ms,c,CONCAT(TEXTSPLIT(ms,,"*")&MID(c,SEQUENCE(LEN(c)),1))))

Excel solution 14 for Replace Masked Characters, proposed by Bhavya Gupta:

=MAP(A2:A10,B2:B10,LAMBDA(ms,c,REDUCE(ms,SEQUENCE(LEN(c)),LAMBDA(x,y,SUBSTITUTE(x,"*",MID(c,y,1),1)))))

Excel solution 15 for Replace Masked Characters, proposed by Charles Roldan:

=MAP(A2:A10,B2:B10,
LAMBDA(x,y,REDUCE(x,MID(y,SEQUENCE(LEN(y)),1),
LAMBDA(a,b,SUBSTITUTE(a,"*",b,1)))))

Excel solution 16 for Replace Masked Characters, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert:

=MAP(A2:A10;B2:B10;LAMBDA(m;n;TEXTJOIN(;;LET(x;MID(m;SEQUENCE(LEN(m));1);IFERROR(XLOOKUP(IF(IFERROR(FIND("*";x;1);"")<>"";SCAN(0;IFERROR(FIND("*";x;1);0);LAMBDA(b;a;SUM(a)+b));"");SEQUENCE(COUNTA(FILTER(x;x="*")));MID(n;SEQUENCE(LEN(n));1));x)))))

Excel solution 17 for Replace Masked Characters, proposed by Stefan Olsson:

=MAP(A2:A10, B2:B10, LAMBDA(strings, chars, REDUCE(strings, REGEXEXTRACT(chars, REPT("(.)", LEN(chars))), LAMBDA(string, chr, REGEXREPLACE(string, "(.*?)(*)(.*)", "$1"&chr&"$3")))))

EDIT: reinstating older, longer version. The shorter version didn't actually work (I blame my eyesight and the small phone screen)

Excel solution 18 for Replace Masked Characters, proposed by Guillermo Arroyo:

=MAP(A2:A10,B2:B10,LAMBDA(_a,_b,REDUCE(_a,MID(_b,SEQUENCE(,LEN(_b)),1),LAMBDA(_c,_d,SUBSTITUTE(_c,"*",_d,1)))))

Excel solution 19 for Replace Masked Characters, proposed by Rayan S.:

=MAP(
 A2:A10,
 B2:B10,
 LAMBDA(a, b,
 LET(
 split, MID(a, SEQUENCE(LEN(a)), 1),
 Char, MID(b, SEQUENCE(LEN(b)), 1),
 table, HSTACK(IFERROR(FIND("*", split, 1), 0), SEQUENCE(LEN(a)), split),
 Cal, IF(
 TAKE(table, , 1) > 0,
 DROP(TAKE(table, , 2), , 1),
 DROP(TAKE(table, , 3), , 2)
 ),
 Pos, SORT(IFERROR(DROP(UNIQUE(Cal + 0), 1), 1)),
 Lookup, HSTACK(Pos, Char),
 Ans, IFERROR(VLOOKUP(Cal, Lookup, 2, 0), Cal),
 TEXTJOIN("", , Ans)
 )
 )
)

Excel solution 20 for Replace Masked Characters, proposed by Fábio Gatti:

=LAMBDA(Text,Chars,Letter,
 LET(
 _Split,TEXTSPLIT(Text,,Letter),
 _nArr,COUNTA(_Split),
 _Seq,SEQUENCE(_nArr),
 _New,MID(" "&Chars,_Seq,1)&_Split,

 TRIM(CONCAT(_New))
 )
)(A2,B2,"*")


Array:
=MAP(A2:A10,B2:B10,LAMBDA(Text,Chars,
 LET(
 _Split,TEXTSPLIT(Text,,"*"),
 _nArr,COUNTA(_Split),
 _Seq,SEQUENCE(_nArr),
 _New,MID(" "&Chars,_Seq,1)&_Split,

 TRIM(CONCAT(_New))
 )
))

Excel solution 21 for Replace Masked Characters, proposed by roberto mensa:

=LET(s,SEQUENCE(1,100),a,SEQUENCE(,LEN(B2)+1),
CONCAT(MID(LEFT(A2,
SMALL(IF(MID(A2&"*",s,1)="*",s),a)-1),
SMALL(IF(MID("*"&A2,s,1)="*",s),a),100)&MID(B2,a,1)))

Solving the challenge of Replace Masked Characters with Excel VBA

Excel VBA solution 1 for Replace Masked Characters, proposed by Anup Kumar:

'Run with care as Undo is not available
Sub ExcelChallenge131()
Dim Cntr As Integer
Dim i As Integer
Dim j As Long
Dim DataRng As Range
Dim Str As String
Dim PadStr As String
On Error GoTo ErrorHandler
Cntr = DataRng.Rows.Count
For i = 1 To Cntr
 Str = DataRng(i, 1)
 PadStr = DataRng(i, 2)
 For j = 1 To Len(PadStr)
 Str = Replace(Str, "*", Mid(PadStr, j, 1), , 1)
 DataRng(i, 1).Offset(0, 4) = Str 'Output is in Column E (4) and corresponding row of input
 Next j
Next i
Exit Sub
ErrorHandler:
MsgBox "Sorry Some Error Happened. Can't Proceed"
End Sub

&&&

Solving the challenge of Replace Masked Characters with Power Query

Solving the challenge of Replace Masked Characters with Excel

Solving the challenge of Replace Masked Characters with Excel VBA

Leave a Reply Cancel reply