Cryptography Challenge – Caesar’s Cipher Shift the each digit by values given in column B. Hence, if Shift value is 1, then 0 becomes 1, 1 becomes 2……8 becomes 9 and 9 becomes 0. If Shift value is 2, then 0>2, 1>3….8>0, 9>1. 0 means no shift. After Shift value 9, same cycle repeats itself. Hence shift value of 10 means shift of 0. Shift value of 15 means shift of 5.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 11
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Caesar Cipher for Digits with Power Query
Power Query solution 1 for Caesar Cipher for Digits, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Split = Table.AddColumn(Source, "Split", each Text.ToList([Text])),
Mod = Table.AddColumn(
Split,
"Calculation",
each List.Transform([Split], (f) => Text.From(Number.Mod(Number.From(f) + [Shift], 10)))
),
Result = Table.AddColumn(Mod, "Result", each Text.Combine([Calculation]))[[Result]]
in
ResultPower Query solution 2 for Caesar Cipher for Digits, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Custom1 = {"0" .. "9"},
Custom2 = Table.AddColumn(
Source,
"Expected Answer",
each
let
z = _,
a = Number.Mod([Shift], 9),
b = Text.Combine(
if [Shift] <= 9 then
List.Transform(
List.Transform(Text.ToList(_[Text]), each Number.From(_)),
each Text.End(Text.From(_ + a), 1)
)
else
List.Transform(
List.Transform(Text.ToList(_[Text]), each Number.From(_)),
each Text.End(Text.From(_ + a - 1), 1)
)
)
in
b
)[[Expected Answer]]
in
Custom2Power Query solution 3 for Caesar Cipher for Digits, proposed by Pavel A.:
let
Input = Table.TransformColumnTypes(
/*hashTag*/table(
{"Text", "Shift"},
{
{"123456789012345", 0},
{"123456789012345", 21},
{"123456789012345", 49},
{"123456789012345", 200}
}
),
{{"Text", type text}, {"Shift", Int64.Type}}
),
proceed = Table.AddColumn(
Input,
"NumberCodedByCaesarEncryption",
each Text.Combine(
List.Transform(
Text.ToList([Text]),
(actChar as text) => Text.From(Number.Mod(Number.From(actChar) + [Shift], 10))
)
),
type text
)
in
proceedPower Query solution 4 for Caesar Cipher for Digits, proposed by Brian Julius:
let
Source = #"Data Raw",
#"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
#"Added DIgitList" = Table.ExpandListColumn(
Table.AddColumn(#"Added Index", "DigitList", each Text.ToList([Text])),
"DigitList"
),
DigitTransform = Table.AddColumn(
#"Added DIgitList",
"Shifted",
each Number.ToText(Number.FromText([DigitList]) + [Shift])
),
#"Split Column by Position" = Table.SplitColumn(
DigitTransform,
"Shifted",
Splitter.SplitTextByPositions({0, 1}, true),
{"Shifted.1", "Shifted.2"}
),
#"Grouped Rows" = Table.Group(
#"Split Column by Position",
{"Text", "Shift"},
{
{
"Expected",
each _,
type table [Text = nullable text, Shift = nullable number, Shifted.2 = nullable text]
}
}
),
#"Added Expected Answer" = Table.RemoveColumns(
Table.AddColumn(
#"Grouped Rows",
"Expected Answer",
each Text.Combine(Table.ToList(Table.SelectColumns([Expected], {"Shifted.2"})))
),
{"Expected"}
)
in
#"Added Expected Answer"Power Query solution 5 for Caesar Cipher for Digits, proposed by Melissa de Korte:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Solution = Table.AddColumn(
Source,
"Solution",
each Text.Combine(
List.Transform(Text.ToList([Text]), (x) => Text.End(Text.From(Number.From(x) + [Shift]), 1))
)
)
in
SolutionPower Query solution 6 for Caesar Cipher for Digits, proposed by Zbigniew Szyszkowski:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source, {{"Text", type text}, {"Shift", Int64.Type}}),
#"Added Custom" = Table.AddColumn(
#"Changed Type",
"Expected Anwer",
(x) =>
Text.Combine(
List.Transform(
Text.ToList(x[Text]),
each Text.From(Number.Mod(Number.From(_) + x[Shift], 10))
)
)
)
in
#"Added Custom"Solving the challenge of Caesar Cipher for Digits with Excel
Excel solution 1 for Caesar Cipher for Digits, proposed by Rick Rothstein:
=CONCAT(RIGHT(B2+MID(A2,SEQUENCE(LEN(A2)),1)))Excel solution 2 for Caesar Cipher for Digits, proposed by محمد حلمي:
=MAP(
A2:A5,
B2:B5,
LAMBDA(
a,
b,
CONCAT(
MOD(
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
)+b,
10
)
)
)
)Excel solution 3 for Caesar Cipher for Digits, proposed by محمد حلمي:
=CONCAT(
RIGHT(
IFERROR(
MID(
A2;
ROW(
$1:$50
);
1
)+B2;
""
);
1
)
)Excel solution 4 for Caesar Cipher for Digits, proposed by Julian Poeltl:
=MAP(
A2:A5,
RIGHT(
B2:B5,
1
),
LAMBDA(
A,
B,
CONCAT(
RIGHT(
MID(
A,
SEQUENCE(
LEN(
A
)
),
1
)+B,
1
)
)
)
)Excel solution 5 for Caesar Cipher for Digits, proposed by Aditya Kumar Darak 🇮🇳:
= MAP(
A2:A5,
B2:B5,
LAMBDA(
a,
b,
CONCAT(
MOD(
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
) + MOD(
b,
10
),
10
)
)
)
)Excel solution 6 for Caesar Cipher for Digits, proposed by Timothée BLIOT:
=CONCAT(RIGHT(MID(A2,SEQUENCE(LEN(A2)),1)+B2,1))Excel solution 7 for Caesar Cipher for Digits, proposed by Duy Tùng:
=--MAP(
A2:A5,
B2:B5,
LAMBDA(
x,
y,
CONCAT(
RIGHT(
REGEXEXTRACT(
x,
".",
1
)+y
)
)
)
)Excel solution 8 for Caesar Cipher for Digits, proposed by Charles Roldan:
=MAP(A2:A5,
B2:B5,
LAMBDA(Text,
Shift,
--LAMBDA(
f,
f(
f
)
)(LAMBDA(f,
LAMBDA(Str,
IF(LEN(
Str
),
RIGHT(
SUM(
LEFT(
Str
),
Shift
)
) & f(
f
)(REPLACE(
Str,
1,
1,
)),
))))(Text)))Excel solution 9 for Caesar Cipher for Digits, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
={SUM(MOD(
MID(
A2,
ROW(
$A$1:INDIRECT(
"A"&LEN(
A2
)
)
),
1
)+MOD(
B2,
10
),
10
)*(10^(LEN(
A2
)-ROW(
$A$1:INDIRECT(
"A"&LEN(
A2
)
)
))))Excel solution 10 for Caesar Cipher for Digits, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert:
=TEXTJOIN(
;
;
IFERROR(
MOD(
IFERROR(
MID(
A2;
ROW(
INDIRECT(
"A1:"&"A"&COUNT(
LEN(
MID(
A2;
ROW(
$A$1:$A$100
);
1
)
)
)
)
);
1
)+B2;
""
);
10
);
""
)
)Excel solution 11 for Caesar Cipher for Digits, proposed by Jardiel Euflázio:
=CONCAT(RIGHT(MID(A2,SEQUENCE(LEN(A2)),1)+B2))Excel solution 12 for Caesar Cipher for Digits, proposed by Jardiel Euflázio:
=CONCAT(MOD(MID(A2,SEQUENCE(LEN(A2)),1)+B2,10))Excel solution 13 for Caesar Cipher for Digits, proposed by Cary Ballard, DML:
=CONCAT(
SCAN(
0,
MID(
A2,
SEQUENCE(
,
LEN(
A2
)
),
1
),
LAMBDA(
a,
v,
RIGHT(
v+B2
)
)
)
)Excel solution 14 for Caesar Cipher for Digits, proposed by Ibrahim Sadiq:
=TEXTJOIN("",
1,
MOD((--MID(
A2,
SEQUENCE(
,
LEN(
A2
),
1
),
1
)+B2),
10))Excel solution 15 for Caesar Cipher for Digits, proposed by Nazmul Islam Jobair:
=CONCAT(RIGHT(--MID(A2,SEQUENCE(LEN(A2)),1)+RIGHT(B2,1),1))Excel solution 16 for Caesar Cipher for Digits, proposed by Charalampos Dimitrakopoulos:
=TEXTJOIN("",,MOD(B2+MID(A2,SEQUENCE(LEN(A2)),1),10))Solving the challenge of Caesar Cipher for Digits with Python in Excel
Python in Excel solution 1 for Caesar Cipher for Digits, proposed by Aditya Kumar Darak 🇮🇳:
data = xl("A1:B5", True)
def MyFun(number, shift):
return "".join(str((int(digit) + shift) % 10) for digit in str(number))
data["Answer"] = data.apply(lambda row: MyFun(row["Text"], row["Shift"]), axis=1)
data