Mask Specific Words in Text

Mask the string with asterisks if a word given in Words column appears in that row. Number of asterisks will be same as the number of characters in the Words. The word in String should appear distinctly, it should not be contained in another word. Hence, if string is “seashore” and replacement word is “sea”, the answer would not be “***shore” but “seashore” only.

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

Solving the challenge of Mask Specific Whole Words with Power Query

Power Query solution 1 for Mask Specific Whole Words, proposed by Bo Rydobon 🇹🇭:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Ans = Table.AddColumn(
    Source, 
    "Answer", 
    each 
      let
        d = Text.Split([Words], ", ")
      in
        Text.Trim(
          Text.Combine(
            List.Transform(
              Splitter.SplitTextByCharacterTransition({"!" .. "z"}, {" " .. "@"})(" " & [String]), 
              each 
                let
                  n = List.PositionOf(d, Text.Lower(Text.Trim(_)))
                in
                  if n < 0 then _ else " " & Text.Repeat("*", Text.Length(d{n}))
            )
          )
        )
  )
in
  Ans

Power Query solution 2 for Mask Specific Whole Words, proposed by Zoran Milokanović:

let
  Source = Excel.CurrentWorkbook(){[Name = "Data"]}[Content], 
  AddedPart = Table.AddColumn(Source, "Part", each Text.Split([String], ",")), 
  ExpandedPart = Table.ExpandListColumn(AddedPart, "Part"), 
  AddedWord = Table.AddColumn(ExpandedPart, "Word", each Text.Split([Part], " ")), 
  AddedWordToSearch = Table.AddColumn(AddedWord, "WordToSearch", each Text.Split([Words], ", ")), 
  Masking = Table.AddColumn(
    AddedWordToSearch, 
    "MaskedText", 
    each List.Accumulate(
      [Word], 
      "", 
      (s, c) =>
        s
          & " "
          & (
            if List.ContainsAny({Text.Lower(c)}, [WordToSearch]) then
              List.Accumulate(Text.ToList(c), "", (se, cu) => se & (if cu = "," then cu else "*"))
            else
              c
          )
    )
  ), 
  Solution = Table.Group(
    Masking, 
    {"String", "Words"}, 
    {{"Answer Expected", each Text.Combine(_[MaskedText], ",")}}
  )[[Answer Expected]]
in
  Solution

Power Query solution 3 for Mask Specific Whole Words, proposed by Alejandro Simón 🇵🇦 🇪🇸:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  String = Table.AddColumn(
    Source, 
    "Answer", 
    each Text.Combine(
      let
        a = Text.Split(Replacer.ReplaceText([String], ",", " ,"), " "), 
        b = Text.Split([Words], ", "), 
        c = List.Transform(b, each Text.Repeat("*", Text.Length(_))), 
        d = List.Zip({b, c}), 
        e = List.ReplaceMatchingItems(a, d, Comparer.OrdinalIgnoreCase)
      in
        e, 
      " "
    )
  )[[Answer]], 
  Sol = Table.TransformColumns(String, {"Answer", each Replacer.ReplaceText(_, " ,", ",")})
in
  Sol

Power Query solution 4 for Mask Specific Whole Words, proposed by Luan Rodrigues:

let
  Fonte = Tabela1, 
  ind = Table.AddIndexColumn(Fonte, "Índice", 0, 1, Int64.Type), 
  tab = Table.AddColumn(ind, "Personalizar", each Text.Split([Words], ", ")), 
  txt = Table.ExpandListColumn(tab, "Personalizar")[[Personalizar], [Índice]], 
  sub = Table.AddColumn(
    txt, 
    "Personalizar.1", 
    each Text.Repeat("*", List.Count(Text.ToList([Personalizar])))
  ), 
  gp = Table.Group(
    sub, 
    {"Índice"}, 
    {{"Contagem", each List.Zip({_[Personalizar], _[Personalizar.1]})}}
  ), 
  tab1 = ind, 
  mesc = Table.NestedJoin(tab1, {"Índice"}, gp, {"Índice"}, "Personalizar1", JoinKind.LeftOuter), 
  exp = Table.ExpandTableColumn(mesc, "Personalizar1", {"Contagem"})[[String], [Contagem]], 
  res = Table.AddColumn(
    exp, 
    "Personalizar", 
    each Text.Replace(
      Text.Combine(
        List.Transform(
          List.ReplaceMatchingItems(
            List.Transform(
              Splitter.SplitTextByCharacterTransition({"A" .. "Z", "a" .. "z", ","}, {" ", ","})(
                [String]
              ), 
              each Text.Trim(_)
            ), 
            [Contagem], 
            Comparer.OrdinalIgnoreCase
          ), 
          Text.From
        ), 
        " "
      ), 
      " ,", 
      ","
    )
  )[[String], [Personalizar]]
in
  res

Power Query solution 5 for Mask Specific Whole Words, proposed by Bhavya Gupta:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Transform = Table.TransformColumns(
    Source, 
    {
      {
        "Words", 
        (w) =>
          List.Transform(
            Text.Split(w, ", "), 
            (y) => {" " & y} & {" " & Text.Repeat("*", Text.Length(y))}
          )
      }
    }
  ), 
  Final = Table.AddColumn(
    Transform, 
    "Answer Expected", 
    each Text.Combine(
      List.ReplaceMatchingItems(
        Splitter.SplitTextByCharacterTransition({"A" .. "Z", "a" .. "z", ","}, {" ", ","})(
          " " & [String]
        ), 
        [Words], 
        Comparer.OrdinalIgnoreCase
      )
    )
  )[[String], [Answer Expected]]
in
  Final

Solving the challenge of Mask Specific Whole Words with Excel

Excel solution 1 for Mask Specific Whole Words, proposed by Bo Rydobon 🇹🇭:

=MAP(A2:A7,B2:B7,LAMBDA(a,b,TRIM(SUBSTITUTE(REDUCE(" "&SUBSTITUTE(a,","," ,")&" "," "&TEXTSPLIT(b&", "&PROPER(b),", ")&" ",LAMBDA(c,v,SUBSTITUTE(c,v," "&REPT("*",LEN(v)-2)&" ")))," ,",","))))

Excel solution 2 for Mask Specific Whole Words, proposed by Bo Rydobon 🇹🇭:

=MAP(A2:A7,B2:B7,LAMBDA(a,b,LET(e,TEXTSPLIT(UPPER(a),CHAR(SEQUENCE(26)+64),,1),c,TEXTSPLIT(a,e),d,TEXTSPLIT(b,", "),
CONCAT(IFNA(XLOOKUP(c,d,REPT("*",LEN(d))),c)&HSTACK(e,"")))))

Excel solution 3 for Mask Specific Whole Words, proposed by Bo Rydobon 🇹🇭:

=MAP(A2:A7,B2:B7,LAMBDA(a,b,LET(c,TEXTSPLIT(a," "),d,TEXTSPLIT(b,", "),e,REPT("*",LEN(d)),TEXTJOIN(" ",,IFNA(XLOOKUP(c,HSTACK(d,d&","),HSTACK(e,e&",")),c)))))

Excel solution 4 for Mask Specific Whole Words, proposed by John V.:

=TRIM(MAP(" "&A2:A7&" ",B2:B7&", "&PROPER(B2:B7),LAMBDA(s,w,REDUCE(s,TEXTSPLIT(w,", "),LAMBDA(i,x,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(i,","," ,")," "&x&" "," "&REPT("*",LEN(x))&" ")," ,",","))))))

Excel solution 5 for Mask Specific Whole Words, proposed by محمد حلمي:

=MAP(A2:A7,B2:B7,LAMBDA(a,b,LET(
r,TEXTSPLIT(a,{" ",","}),
c,TEXTSPLIT(b,", "),
TEXTJOIN(" ",,
IFNA(XLOOKUP(r,c,REPT("*",LEN(c))),r)))))

Excel solution 6 for Mask Specific Whole Words, proposed by محمد حلمي:

=MAP(A2:A7,B2:B7,LAMBDA(a,b,LET(
s,TEXTSPLIT(a," "),
c,TEXTSPLIT(b,", "),
e,REPT("*",LEN(c)),
TEXTJOIN(" ",,IFNA(IFNA(
XLOOKUP(s,c,e),XLOOKUP(s,c&",",e&",")),s)))))

Excel solution 7 for Mask Specific Whole Words, proposed by 🇰🇷 Taeyong Shin:

=MAP(A2:A7, B2:B7, LAMBDA(a,b,
 LET(
 s, TEXTSPLIT(a, , {" ",","}),
 w, TEXTSPLIT(b, , ", "),
 SUBSTITUTE(TEXTJOIN(" ", 0, IFNA(XLOOKUP(s, w, REPT("*", LEN(w))), s)), "  ", ", ")
 )
))

Excel solution 8 for Mask Specific Whole Words, proposed by Kris Jaganah:

=MAP(A2:A7,B2:B7,LAMBDA(x,y,LET(a,TEXTSPLIT(y,,", "),b,LEN(a),c,SEQUENCE(ROWS(a)),d,REPT("*",b),e,TEXTSPLIT(x,," "),f,IFNA(XMATCH(SUBSTITUTE(e,",",""),a),0),g,IFERROR(XLOOKUP(f,c,d),e),TEXTJOIN(" ",1,IF(LEN(e)=LEN(g),g,g&",")))))

Excel solution 9 for Mask Specific Whole Words, proposed by Julian Poeltl:

Excel solution 10 for Mask Specific Whole Words, proposed by Aditya Kumar Darak 🇮🇳:

=LET(
 _st, A2:A7,
 _wd, B2:B7,
 _e1, LAMBDA(x, y,
 LET(
 c1, ISNUMBER(SEARCH(y, x)),
 cd, IFERROR(CODE(MID(x, LEN(y) + 1, 1)), 32),
 c2, (cd < 97) + (cd > 122),
 r, IF(c1 * c2, REPT("*", LEN(y)) & REPT(CHAR(cd), c2), x),
 r
 )
 ),
 _e2, LAMBDA(a, b,
 CONCAT(REDUCE(TEXTSPLIT(a, " "), TEXTSPLIT(b, ", "), _e1) & " ")
 ),
 _r, TRIM(MAP(_st, _wd, _e2)),
 _r
)

Excel solution 11 for Mask Specific Whole Words, proposed by Timothée BLIOT:

=MAP(
    A2:A7,
    B2:B7,
    LAMBDA(
        a,
        b,
        SUBSTITUTE(
            TEXTJOIN(
                "",
                ,
                MAP(
                    TEXTSPLIT(
                        SUBSTITUTE(
                            a,
                            ",",
                            ","
                        ),
                        ""
                    ),
                    LAMBDA(
                        x,
                        IF(
                            ISNUMBER(
                                XMATCH(
                                    x,
                                    TEXTSPLIT(
                                        b,
                                        ","
                                    )
                                )
                            ),
                            REPT(
                                "*",
                                LEN(
                                    x
                                )
                            ),
                            x
                        )
                    )
                )
            ),
            ",",
            ","
        )
    )
)

Excel solution 12 for Mask Specific Whole Words, proposed by Sunny Baggu:

=MAP(A2:A7,B2:B7,LAMBDA(a,b,
LET(_stringsplit,TEXTSPLIT(SUBSTITUTE(a,","," ,"),," "),
_wordsplit,TEXTSPLIT(b,,", "),
_fill,REPT("*",LEN(_wordsplit)),
_xlk,XLOOKUP(_stringsplit,_wordsplit,_fill,""),
_ft,IF(LEFT(_xlk)<>"*",_stringsplit,_xlk),
SUBSTITUTE(TRIM(CONCAT(_ft&" "))," ,",","))))

Excel solution 13 for Mask Specific Whole Words, proposed by Bhavya Gupta:

=MAP(A2:A7,B2:B7,LAMBDA(s,w,LET(a,TEXTSPLIT(SUBSTITUTE(s,","," ,"),," "),b,TEXTSPLIT(w,,", "),SUBSTITUTE(CONCAT(IFNA(XLOOKUP(a,b,REPT("*",LEN(b))),a)&" ")," ,",","))))

Excel solution 14 for Mask Specific Whole Words, proposed by Md. Zohurul Islam:

Excel solution 15 for Mask Specific Whole Words, proposed by Charles Roldan:

=MAP(A2:A7,B2:B7,LAMBDA(x,y,SUBSTITUTE(
REDUCE(x,TEXTSPLIT(y&", "&PROPER(y),", "),
LAMBDA(a,b,SUBSTITUTE(
" "&SUBSTITUTE(a,","," ,")&" "," "&b&" ",
" "&REPT("*",LEN(b))&" ")))," ","")))

Excel solution 16 for Mask Specific Whole Words, proposed by Stefan Olsson:

=BYROW(A2:B7, 
LAMBDA(br, 
REDUCE(INDEX(br, ,1), SPLIT(INDEX(br, ,2), ", ", TRUE, TRUE), 
LAMBDA(s, w, 
REGEXREPLACE(s, "(?i)(b"&w&"b)", REPT("*", LEN(w))
)))))

Excel solution 17 for Mask Specific Whole Words, proposed by Abhishek Kumar Jain:

=MAP(
A2:A7,
B2:B7,
LAMBDA(x,y,
LET(
a,TEXTSPLIT(x," "),
b,TEXTSPLIT(y,", "),
c,IF(RIGHT(a)=",",LEFT(a,LEN(a)-1),a),
TEXTJOIN(" ",1,IFNA(XLOOKUP(c,b,REPT("*",LEN(b))),c)&IF(RIGHT(a)=",",",","")))))

Excel solution 18 for Mask Specific Whole Words, proposed by Guillermo Arroyo:

=MAP(A2:A7,B2:B7,LAMBDA(_a,_b,LET(_c,TEXTSPLIT(_b,", "),TRIM(REDUCE("",TEXTSPLIT(_a," "),LAMBDA(_d,_e,_d&" "&LET(_p,TEXTBEFORE(_e,",",,,,_e),IF(OR(_p=_c),REPT("*",LEN(_p)),_p)&IF(RIGHT(_e)=",",",",""))))))))

Excel solution 19 for Mask Specific Whole Words, proposed by Rayan S.:

=MAP(
 A2:A7,
 B2:B7,
 LAMBDA(a, b,
 LET(
 Words, TEXTSPLIT(b, ", "),
 String, TEXTSPLIT(SUBSTITUTE(a, ",", " ,", 1), " "),
 Match, HSTACK(TOCOL(Words), TOCOL(REPT("*", LEN(Words)))),
 Find, XLOOKUP(TOCOL(String), TAKE(Match, , 1), DROP(Match, , 1), "", 0),
 Ans, TEXTJOIN(" ", , IF(Find = "", TOCOL(String), Find)),
 SUBSTITUTE(Ans, " ,", ",", 1)
 )
 )
)

&&&

Solving the challenge of Mask Specific Whole Words with Power Query

Solving the challenge of Mask Specific Whole Words with Excel

Leave a Reply Cancel reply