We had 3 challenges on Caesar’s Cipher (if you are unaware about Caesar’s Cipher, I will encourage you to visit previous challenge to get an idea) This is a variation of Caesar’s Cipher. Starting character is shifted by number given in Shift. Every subsequent character is shifted by + 1, +2, +3 and so on. Hence, if word is “War” and shift is 5, then “W” will be shifted by 5, “a” by 6 and “r” by 7. Hence, answer would be “Bgy”. This is a case sensitive problem.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 236
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Incremental Caesar Cipher with Power Query
Power Query solution 1 for Incremental Caesar Cipher, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Ans = Table.TransformRows(
Source,
each Text.Combine(
List.Transform(
{0 .. Text.Length([Text]) - 1},
(n) =>
let
c = Character.ToNumber(Text.Middle([Text], n, 1)),
m = Number.Mod(c, 32)
in
Character.FromNumber(c + Number.Mod(m + n + [Shift] - 1, 26) + 1 - m)
)
)
)
in
AnsPower Query solution 2 for Incremental Caesar Cipher, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
S = Table.TransformRows(
Source,
each
let
a = {"a" .. "z", "A" .. "Z"},
t = Text.ToList([Text])
in
List.Accumulate(
List.Positions(t),
"",
(s, c) =>
s
& (
let
p = List.PositionOf(a, t{c}),
s = 26
* Number.RoundDown(p / 26) + Number.Mod(Number.Mod(p, 26) + c + [Shift], 26)
in
a{s}
)
)
)
in
SPower Query solution 3 for Incremental Caesar Cipher, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.AddColumn(
Source,
"Answer",
each
let
z = Text.ToList([Text]),
a = List.Zip({{"a" .. "z"}, {0 .. 25}}),
b = List.ReplaceMatchingItems(z, a, Comparer.OrdinalIgnoreCase),
c = List.Transform(
{0 .. List.Count(z) - 1},
(x) => Number.Mod(b{x} + {[Shift] .. ([Shift] + Text.Length([Text]) - 1)}{x}, 26)
),
d = Text.Combine(
List.Transform(
{0 .. List.Count(z) - 1},
each try
if z{_} = Text.Upper(z{_}) then {"A" .. "Z"}{c{_}} else {"a" .. "z"}{c{_}}
otherwise
" "
)
)
in
d
)[[Answer]]
in
SolPower Query solution 4 for Incremental Caesar Cipher, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
res = Table.AddColumn(
Fonte,
"Result",
each [
a = List.Transform({0 .. Text.Length([Text])}, (x) => [Shift] + x),
b = List.Transform(Text.ToList([Text]), each List.PositionOf({"A" .. "Z"}, _)),
c = List.Transform(
List.Transform(
List.Transform(List.Select(List.Zip({a, b}), each _{1} >= 0), List.Sum),
each Number.Mod(_, 26)
),
(x) => {"A" .. "Z"}{x}
),
d = List.Transform(Text.ToList([Text]), each List.PositionOf({"a" .. "z"}, _)),
e = List.Transform(
List.Transform(
List.Transform(List.Select(List.Zip({a, d}), each _{1} >= 0), List.Sum),
each Number.Mod(_, 26)
),
(x) => {"a" .. "z"}{x}
),
f = Text.Combine(List.Combine({c, e}))
][f]
)
in
resPower Query solution 5 for Incremental Caesar Cipher, proposed by Venkata Rajesh:
let
Source = Data,
Output = Table.AddColumn(
Source,
"Output",
each [
w = Text.ToList([Text]),
x = List.Count(w),
y = {[Shift] .. [Shift] + x - 1},
z = Text.Combine(
List.Transform(
{0 .. x - 1},
each [
a = List.PositionOf({"a" .. "z"}, Text.Lower(w{_})),
b = Number.Mod(a + y{_}, 26),
c = if w{_} = Text.Upper(w{_}) then {"A" .. "Z"}{b} else {"a" .. "z"}{b}
][c]
)
)
][z]
)
in
OutputPower Query solution 6 for Incremental Caesar Cipher, proposed by Fatemeh Heydari:
let
Source = Excel.CurrentWorkbook(){[Name = "Data"]}[Content],
ShiftText = (text as text, shift as number) as text =>
let
alphabet = {"a" .. "z"},
shiftedChars = List.Transform(
{0 .. Text.Length(text) - 1},
each
let
A = Character.ToNumber(Text.Middle(text, _, 1)),
B = Number.Mod(A, 32),
C = Character.FromNumber(A + Number.Mod(B + _ + shift - 1, 26) + 1 - B)
in
C
),
ResultText = Text.Combine(shiftedChars)
in
ResultText,
Result = Table.AddColumn(Source, "Result", each ShiftText([Text], [Shift])),
FinalResult = Table.RemoveColumns(Result, {"Shift"})
in
FinalResultSolving the challenge of Incremental Caesar Cipher with Excel
Excel solution 1 for Incremental Caesar Cipher, proposed by Bo Rydobon 🇹🇭:
=MAP(A2:A10,B2:B10,LAMBDA(a,s,LET(n,SEQUENCE(LEN(a)),c,CODE(MID(a,n,1)),m,MOD(c,32),CONCAT(CHAR(c+MOD(m+n+s-2,26)+1-m)))))Excel solution 2 for Incremental Caesar Cipher, proposed by John V.:
=MAP(A2:A10,B2:B10,LAMBDA(a,b,LET(s,SEQUENCE(LEN(a)),c,CODE(MID(a,s,1)),n,97-32*(c<91),CONCAT(CHAR(n+MOD(c+b+s-n-1,26))))))Excel solution 3 for Incremental Caesar Cipher, proposed by محمد حلمي:
=MAP(A2:A10,B2:B10,LAMBDA(a,b,LET(i,SEQUENCE(LEN(a)),v,CODE(MID(a,i,1)),SUBSTITUTE(CONCAT(CHAR(MOD(MOD(v,32)+i+b-1,26)+(v>91)*32+64)),"`","z"))))Excel solution 4 for Incremental Caesar Cipher, proposed by Kris Jaganah:
=MAP(A2:A10,B2:B10,LAMBDA(x,y,LET(a,SEQUENCE(26,,65),b,CHAR(a),c,SEQUENCE(LEN(x)),d,MID(x,c,1),e,CHAR(REDUCE(XLOOKUP(d,b,a)+c-1+y,SEQUENCE(9),LAMBDA(v,w,IF(v>90,v-26,v)))),CONCAT(IF(EXACT(d,UPPER(d)),e,LOWER(e))))))Excel solution 5 for Incremental Caesar Cipher, proposed by Julian Poeltl:
=MAP(A2:A10,B2:B10,LAMBDA(T,Sh,LET(SP,MID(T,SEQUENCE(LEN(T)),1),L,LOWER(SP),C,CHAR(MOD(CODE(L)+SEQUENCE(LEN(T),,Sh)-97,26)+97),CONCAT(IF(EXACT(SP,L),C,UPPER(C))))))Excel solution 6 for Incremental Caesar Cipher, proposed by Timothée BLIOT:
=MAP(A2:A10,B2:B10,LAMBDA(v,w,LET(A,MID(v,SEQUENCE(LEN(v)),1),B,MAP(A,LAMBDA(x,EXACT(x,UPPER(x)))),D,CODE(LOWER(A))-97,CONCAT(MAP(SEQUENCE(ROWS(D)),LAMBDA(x,LET(E,CHAR(MOD(INDEX(D,x)+w+x-1,26)+97),IF(INDEX(B,x),UPPER(E),E))))))))Excel solution 7 for Incremental Caesar Cipher, proposed by Hussein SATOUR:
=MAP(A2:A10, B2:B10, LAMBDA(x,y, LET(
a, SEQUENCE(LEN(x)), b, MID(x, a,1),
c, EXACT(b, UPPER(b)), d, CODE(LOWER(b)) + a + y - 1,
e, MOD(d-96, 26) + 96, f, CHAR(IF(e=96, 122, e)),
CONCAT(IF(c, UPPER(f), f)))))Excel solution 8 for Incremental Caesar Cipher, proposed by Oscar Mendez Roca Farell:
=MAP(A2:A10, B2:B10, LAMBDA(a, b, LET(_n,LEN(a),_m, CODE(MID(a,SEQUENCE(_n), 1)),_f, 32*(2+(_m>96)), CONCAT(CHAR(IFERROR((1/MOD(_m-_f+SEQUENCE(_n, ,b), 26))^-1, 26)+_f)))))Excel solution 9 for Incremental Caesar Cipher, proposed by Sunny Baggu:
=LET(
_up, CHAR(SEQUENCE(26, , 65)),
_upnum, WRAPCOLS(SEQUENCE(MAX(B2:B10) + 26 * 2, , 65), 26, 0),
_low, CHAR(SEQUENCE(26, , 97)),
_lownum, WRAPCOLS(SEQUENCE(MAX(B2:B10) + 26 * 2, , 97), 26, 0),
MAP(
A2:A10,
B2:B10,
LAMBDA(a, b,
LET(
_code, CODE(MID(a, SEQUENCE(LEN(a)), 1)),
_sum, _code + SEQUENCE(LEN(a)) - 1 + b,
CONCAT(
MAP(
_code,
_sum,
LAMBDA(x, y,
IF(x < 97, TOCOL(IFS(_upnum = y, _up), 3), TOCOL(IFS(_lownum = y, _low), 3))
)
)
)
)
)
)
)Excel solution 10 for Incremental Caesar Cipher, proposed by LEONARD OCHEA 🇷🇴:
=MAP(A2:A10,B2:B10,LAMBDA(a,b,LET(l,LEN(a),c,CODE(MID(a,SEQUENCE(l),1)),d,c+SEQUENCE(l,,b),e,d-26*INT((d-IF(c<91,65,97))/26),CONCAT(CHAR(e)))))Excel solution 11 for Incremental Caesar Cipher, proposed by JvdV –:
=MAP(A2:A10,B2:B10,LAMBDA(a,b,LET(l,SEQUENCE(LEN(a)),c,CODE(MID(a,l,1)),y,65+32*(c>96),CONCAT(CHAR(MOD(c-y-1+b+l,26)+y)))))Excel solution 12 for Incremental Caesar Cipher, proposed by Julien Lacaze:
=LET(t,A2:A10,s,B2:B10,
isUpper,LAMBDA(text,LET(c,CODE(text),--(c>=CODE("A"))*(c<=CODE("Z")))),
isLower,LAMBDA(text,LET(c,CODE(text),--(c>=CODE("a"))*(c<=CODE("z")))),
Cipher,LAMBDA(text,shift,
LET(split,MID(text,SEQUENCE(LEN(text)),1),
IFS(isUpper(split), CHAR(MOD(CODE(split)-CODE("A")+SEQUENCE(LEN(text),,shift),26)+CODE("A")),
isLower(split), CHAR(MOD(CODE(split)-CODE("a")+SEQUENCE(LEN(text),,shift),26)+CODE("a")),
1,split
)
)),
MAP(t,s,LAMBDA(t,s,CONCAT(Cipher(t,s)))))
- Reused defined Lambda for challenge 232 (isUpper, isLower)
- Used Sequence( len_of_text, , shift_value_as_start) to add +1 on each letter.
- MID(text, Sequence(len(text) to split as usual
My other solution, when trying to get the shortest length :
=MAP(A2:A10,B2:B10,LAMBDA(t,s,CONCAT(LET(e,CODE(MID(t,SEQUENCE(LEN(t)),1)),CHAR(MOD(e-65-32*(e>96)+SEQUENCE(LEN(t),,s),26)+65+32*(e>96))))))Excel solution 13 for Incremental Caesar Cipher, proposed by Ziad A.:
=MAP(A2:A10,B2:B10,LAMBDA(t,s,LET(l,SEQUENCE(LEN(t)),k,CODE(MID(t,l,1))+MOD(l-1+s,26),c,CODE(MID(t,l,1)),TEXTJOIN(,,CHAR(IFS(k>122,97+MOD(k,123),(k>90)*(c>64)*(c<91),65+MOD(k,91),1,k))))))Excel solution 14 for Incremental Caesar Cipher, proposed by Daniel Garzia:
=MAP(A2:A10,B2:B10,LAMBDA(l,k,LET(s,SEQUENCE(LEN(l)),c,CODE(MID(l,s,1)),t,IF(c<97,65,97),CONCAT(CHAR(t+MOD(c-t-1+s+k,26))))))Excel solution 15 for Incremental Caesar Cipher, proposed by Henriette Hamer:
=MAP(
A2:A10;
B2:B10;
LAMBDA(_alltext;_all_a;
LET(
_text;_alltext;
_a;_all_a;
_b;SEQUENCE(;LEN(_text);0);
_much_used_1;CODE(MID(_text;SEQUENCE(;LEN(_text));1));
TEXTJOIN(
"";
FALSE;
CHAR(
IF(
_much_used_1=32;
32;
IF(
_much_used_1<90;
MOD(+IF(_much_used_1=32;32;IF(_much_used_1<90;_much_used_1-65;_much_used_1-97))+_a+_b;26)+65;
MOD(+IF(_much_used_1=32;32;IF(_much_used_1<90;_much_used_1-65;_much_used_1-97))+_a+_b;26)+97
)
)
)
)
)
)
)Excel solution 16 for Incremental Caesar Cipher, proposed by Harry Seiders:
=MAP(A2:A10,B2:B10,LAMBDA(Z,D,LET(len,LEN(Z),addon,SEQUENCE(len,,D),Breakupwrd,CODE(MID(Z,SEQUENCE(len),1)),start,IF(Breakupwrd>=97,97,65),TEXTJOIN(,FALSE,CHAR(MOD(Breakupwrd-start+addon,26)+start)))))&&&