XOR Cipher for Message

This time, in step 3 in place of adding both the numbers, we need to do Bitwise XOR operation. 1. For a given message, a key is chosen having the same length. 2. Assign a=0, b=1,……y=24, z=25 to English alphabets to both the message as well as the key. 3. Bitwise XOR both the numbers for corresponding English alphabet numbers for both the message as well as the key 4. If result > 25, then subtract 26 from it otherwise leave as it is. 5. For the resultant number, assign the alphabets as per step 2. Example: Message = war and Key = tom w=22, a=0, r=17 t=19, o=14, m=12 Bitwise XOR of these = 5, 14, 29 29 is > 25, hence reduce by 26. Hence final numbers are 5, 14, 3 English alphabets corresponding to these numbers are – f, o, d Hence encrypted text will become “fod”.

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

Solving the challenge of XOR Cipher for Message with Power Query

Power Query solution 1 for XOR Cipher for Message, proposed by Bo Rydobon 🇹🇭:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Comb = List.Transform(
    Table.ToRows(Source), 
    each Text.Combine(
      List.Transform(
        {0 .. Text.Length(_{0}) - 1}, 
        (n) =>
          let
            C = List.Transform(_, each Character.ToNumber(Text.ToList(_){n}) - 97)
          in
            Character.FromNumber(Number.Mod(Number.BitwiseXor(C{0}, C{1}), 26) + 97)
      )
    )
  )
in
  Comb

Power Query solution 2 for XOR Cipher for Message, proposed by Zoran Milokanović:

let
  Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content], 
  Assignment = List.Zip({{"a" .. "z"}, {0 .. 25}}), 
  Calculation = Table.AddColumn(
    Source, 
    "EM", 
    each 
      let
        M = List.ReplaceMatchingItems(Text.ToList([Message]), Assignment), 
        K = List.ReplaceMatchingItems(Text.ToList([Key]), Assignment)
      in
        List.Accumulate(
          List.Positions(M), 
          "", 
          (s, d) =>
            s
              & List.ReplaceMatchingItems(
                {Number.Mod(Number.BitwiseXor(M{d}, K{d}), 26)}, 
                List.Transform(Assignment, each List.Reverse(_))
              ){0}
        )
  ), 
  Solution = Calculation[EM]
in
  Solution

Power Query solution 3 for XOR Cipher for Message, proposed by Aditya Kumar Darak 🇮🇳:

let
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  Return = Table.AddColumn(
    Source, 
    "Answer", 
    each [
      L = Text.Length([Message]), 
      G = {0 .. L - 1}, 
      T = List.Transform(
        G, 
        (f) =>
          [
            M  = Character.ToNumber(Text.At([Message], f)) - 97, 
            K  = Character.ToNumber(Text.At([Key], f)) - 97, 
            BT = Number.Mod(Number.BitwiseXor(M, K), 26), 
            R  = Character.FromNumber(BT + 97)
          ][R]
      ), 
      C = Text.Combine(T)
    ][C]
  )
in
  Return

Power Query solution 4 for XOR Cipher for Message, proposed by Alejandro Simón 🇵🇦 🇪🇸:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Zip = List.Zip({{"a" .. "z"}, {0 .. 25}}), 
  Transform = Table.TransformColumns(
    Source, 
    List.Transform(
      {"Message", "Key"}, 
      each {_, each List.ReplaceMatchingItems(Text.ToList(_), Zip)}
    )
  ), 
  Sol = Table.AddColumn(
    Transform, 
    "Encrypted", 
    (x) =>
      Text.Combine(
        List.ReplaceMatchingItems(
          List.Transform(
            {0 .. List.Count(x[Key]) - 1}, 
            each Number.Mod(Number.BitwiseXor(x[Message]{_}, x[Key]{_}), 26)
          ), 
          List.Transform(Zip, List.Reverse)
        ), 
        ""
      )
  )[[Encrypted]]
in
  Sol

Power Query solution 5 for XOR Cipher for Message, proposed by Luan Rodrigues:

let
  Fonte = Tabela1, 
  lits = List.Zip({{"a" .. "z"}, {0 .. 25}}), 
  lits2 = List.Zip({{0 .. 25}, {"a" .. "z"}}), 
  res = Table.AddColumn(
    Fonte, 
    "Personalizar", 
    each [
      a = List.ReplaceMatchingItems(Text.ToList([Message]), lits), 
      b = List.ReplaceMatchingItems(Text.ToList([Key]), lits), 
      c = List.Transform(List.Zip({a, b}), each Number.BitwiseXor(_{0}, _{1})), 
      d = List.Transform(c, (x) => if x > 25 then x - 26 else x), 
      e = Text.Combine(List.ReplaceMatchingItems(d, lits2))
    ][e]
  )
in
  res

Power Query solution 6 for XOR Cipher for Message, proposed by Alexis Olson:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  #"Changed Type" = Table.TransformColumnTypes(Source, {{"Message", type text}, {"Key", type text}}), 
  #"Added Custom" = Table.AddColumn(
    #"Changed Type", 
    "Encryoted Message", 
    each 
      let
        message2nums = List.Transform(Text.ToList([Message]), each Character.ToNumber(_) - 97), 
        key2nums = List.Transform(Text.ToList([Key]), each Character.ToNumber(_) - 97), 
        XOR_nums = List.Transform(
          List.Zip({message2nums, key2nums}), 
          (pair) => Number.Mod(Number.BitwiseXor(pair{0}, pair{1}), 26)
        ), 
        Result = Text.Combine(List.Transform(XOR_nums, each Character.FromNumber(_ + 97)))
      in
        Result, 
    type text
  )
in
  #"Added Custom"

Power Query solution 7 for XOR Cipher for Message, proposed by Guillermo Arroyo:

let
  Origen = Excel.CurrentWorkbook(){[Name = "Tabla1"]}[Content], 
  a = List.Zip(
    {
      List.Transform(Origen[Message], each Text.ToList(_)), 
      List.Transform(Origen[Key], each Text.ToList(_))
    }
  ), 
  b = List.Transform(
    a, 
    (c) =>
      let
        o = List.Transform(c{0}, each (Character.ToNumber(_) - 97)), 
        p = List.Transform(c{1}, each (Character.ToNumber(_) - 97)), 
        q = List.Zip({o, p}), 
        r = List.Transform(
          q, 
          each Character.FromNumber(Number.Mod(Number.BitwiseXor(_{0}, _{1}), 26) + 97)
        )
      in
        Text.Combine(r)
  )
in
  b

Solving the challenge of XOR Cipher for Message with Excel

Excel solution 1 for XOR Cipher for Message, proposed by Bo Rydobon 🇹🇭:

=BYROW(A2:B7,LAMBDA(a,LET(b,CODE(MID(a,SEQUENCE(MIN(LEN(a))),1))-97,CONCAT(CHAR(MOD(BITXOR(TAKE(b,,1),DROP(b,,1)),26)+97)))))

Excel solution 2 for XOR Cipher for Message, proposed by Rick Rothstein:

=LET(f,LAMBDA(w,CODE(MID(LOWER(w),SEQUENCE(LEN(w)),1))-97),MAP(A2:A7,B2:B7,LAMBDA(x,y,CONCAT(MAP(f(x),f(y),LAMBDA(a,b,CHAR(97+MOD(BITXOR(a,b),26))))))))

Excel solution 3 for XOR Cipher for Message, proposed by Rick Rothstein:

=MAP(A2:A7,B2:B7,LAMBDA(a,b,LET(s,SEQUENCE(LEN(a)),CONCAT(CHAR(97+MOD(BITXOR(CODE(MID(a,s,1))-97,CODE(MID(b,s,1))-97),26))))))

Excel solution 4 for XOR Cipher for Message, proposed by John V.:

=MAP(A2:A7,B2:B7,LAMBDA(m,k,LET(f,LAMBDA(t,CODE(MID(t,SEQUENCE(LEN(t)),1))-97),CONCAT(CHAR(97+MOD(BITXOR(f(m),f(k)),26))))))

Excel solution 5 for XOR Cipher for Message, proposed by محمد حلمي:

=MAP(A2:A7,B2:B7,LAMBDA(C,V,LET(
H,SEQUENCE(26,,0),
R,CHAR(ROW(97:122)),
F,LAMBDA(X,
XLOOKUP(MID(X,SEQUENCE(LEN(X)),1),R,H)),
CONCAT(
XLOOKUP(MOD(BITXOR(F(C),F(V)),26),H,R)))))

Excel solution 6 for XOR Cipher for Message, proposed by 🇰🇷 Taeyong Shin:

=REDUCE("", SEQUENCE(MAX(LEN(A2:B7))), LAMBDA(a,n, LET( x, CODE(MID(A2:B7, n, 1)) - 97, a & IFERROR(CHAR(MOD(BITXOR(TAKE(x, , 1), TAKE(x, , -1)), 26) + 97), "")) ))

Excel solution 7 for XOR Cipher for Message, proposed by Kris Jaganah:

MAP(A2:A7,B2:B7,LAMBDA(x,y,LET(a,BITXOR(CODE(MID(x,SEQUENCE(LEN(x)),1))-97,CODE(MID(y,SEQUENCE(LEN(y)),1))-97),b,CONCAT(CHAR(IF(a>25,a-26,a)+97)),b)))

Excel solution 8 for XOR Cipher for Message, proposed by Julian Poeltl:

=MAP(A2:A7,B2:B7,LAMBDA(M,K,LET(SP,LAMBDA(A,CODE(MID(A,SEQUENCE(LEN(A)),1))-97),CONCAT(CHAR(97+MOD(BITXOR(SP(M),SP(K)),26))))))

Excel solution 9 for XOR Cipher for Message, proposed by Aditya Kumar Darak 🇮🇳:

=MAP(
 A2:A7,
 B2:B7,
 LAMBDA(a, b,
 LET(
 sq, SEQUENCE(LEN(a)),
 s, MID(HSTACK(a, b), sq, 1),
 cd, CODE(s) - 97,
 bt, MOD(BITXOR(TAKE(cd, , 1), TAKE(cd, , -1)), 26),
 r, CONCAT(CHAR(bt + 97)),
 r
 )
 )
)

Excel solution 10 for XOR Cipher for Message, proposed by Timothée BLIOT:

=LET(F,LAMBDA(x,CODE(MID(x,SEQUENCE(LEN(x)),1))-97),MAP(A2:A7,B2:B7,LAMBDA(a,b,CONCAT(CHAR(MOD(BITXOR(F(a),F(b)),26)+97)))))

Excel solution 11 for XOR Cipher for Message, proposed by Sunny Baggu:

=MAP(A2:A7,B2:B7,LAMBDA(a,b,
LET(_alpha,CHAR(SEQUENCE(26,,CODE("a"))),_num,SEQUENCE(26)-1,
_e1,LAMBDA(x,MID(x,SEQUENCE(LEN(x)),1)),
_e2,LAMBDA(_arr1,_arr2,_arr3,XLOOKUP(_arr1,_arr2,_arr3)),
_mes,_e1(a),
_key1,_e1(b),
_mesnum,_e2(_mes,_alpha,_num),
_keynum,_e2(_key1,_alpha,_num),
_sum,IF((_mesnum+_keynum)>25,_mesnum+_keynum-26,_mesnum+_keynum),
_encryo,_e2(_sum,_num,_alpha),CONCAT(_encryo))))

Excel solution 12 for XOR Cipher for Message, proposed by Md. Zohurul Islam:

=LET(u,A2:A7,v,B2:B7,
w,CHAR(SEQUENCE(26,,97)),n,SEQUENCE(26,,0),
f,LAMBDA(p,XLOOKUP(MID(p,SEQUENCE(LEN(p)),1),w,n)),
z,MAP(u,v,LAMBDA(x,y,LET(
 a,BITXOR(f(x),f(y)),
 b,IF(a>25,a-26,a),
 d,CONCAT(XLOOKUP(b,n,w)),
 d))),
z)

Excel solution 13 for XOR Cipher for Message, proposed by Julien Lacaze:

=MAP(A2:A8,B2:B8,LAMBDA(a,b,
LET(
_msg,IFERROR(CODE(MID(CHOOSEROWS(a,SEQUENCE(ROWS(a))),SEQUENCE(1,MAX(LEN(b))),1))-CODE("a"),""),
_key,IFERROR(CODE(MID(CHOOSEROWS(b,SEQUENCE(ROWS(a))),SEQUENCE(1,MAX(LEN(b))),1))-CODE("a"),""),
_res,BYROW(a,LAMBDA(arr,CONCAT(IFERROR(CHAR(MOD(BITXOR(_msg,_key),26)+CODE("a")),"")))),
_res
)))

Excel solution 14 for XOR Cipher for Message, proposed by Guillermo Arroyo:

=LET(g,LAMBDA(h,CODE(MID(h,SEQUENCE(LEN(h)),1))-97),MAP(A2:A7,B2:B7,LAMBDA(a,b,CONCAT(MAP(g(a),g(b),LAMBDA(i,j,CHAR(MOD(BITXOR(i,j),26)+97)))))))

Excel solution 15 for XOR Cipher for Message, proposed by Henriette Hamer:

MAP(A2:A7;B2:B7;LAMBDA(a;b;TEXTJOIN("";TRUE;CHAR(MOD(BITXOR(CODE(MID(a;SEQUENCE(LEN(a);;1;1);1))-97;CODE(MID(b;SEQUENCE(LEN(b);;1;1);1))-97);26)+97))))

Excel solution 16 for XOR Cipher for Message, proposed by Stéphane T.:

=BYROW(A2:B7;LAMBDA(l;CONCAT(CAR(MOD(REDUCE(0;l;LAMBDA(a;v;BITOUEXCLUSIF(a;CODE(STXT(v;SEQUENCE(NBCAR(v));1))-97)));26)+97))))

and in English
=BYROW(A2:B7,LAMBDA(l,CONCAT(CHAR(MOD(REDUCE(0,l,LAMBDA(a,v,BITXOR(a,CODE(MID(v,SEQUENCE(LEN(v)),1))-97))),26)+97))))

Excel solution 17 for XOR Cipher for Message, proposed by Ben Gutscher:

=CONCAT(LET(pos,SEQUENCE(,LEN(A2)),msgltr,MID(A2,pos,1),keyltr,MID(B2,pos,1),CHAR(MOD(BITXOR(CODE(msgltr)-97,CODE(keyltr)-97),26)+97)))

&&&

Solving the challenge of XOR Cipher for Message with Power Query

Solving the challenge of XOR Cipher for Message with Excel

Leave a Reply Cancel reply