This is the 3rd in series of Caesar’s Cipher Challenges. I had published 2 challenges on Caesar’s Cipher earlier (links at the bottom of the post) and this is the 3rd one. Shift each alphabet and number by values given in column B. If Shift value is 2, In case of alphabets – then A>C, B>D….Y>A, Z>B In case of numbers – then 0>2, 3>5, 9-1 0 means no shift. After Shift value of 25 in case of alphabets and 9 in case of numbers, same cycle repeats itself. Hence, shift of 26, 52, 78… is equal to shift of 0 in case of alphabets and 10, 20, 30… is equal to 0 in case of numbers. A character other than alphabet and number should not be shifted. – Earlier challenges on this topic – This challenge dealt with Caesar’s shift for numbers. This challenge dealt with Caesar’s shift for alphabets.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 103
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Caesar Cipher Multi Shift with Power Query
Power Query solution 1 for Caesar Cipher Multi Shift, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Rs = Table.TransformRows(
Source,
each Text.Combine(
List.Transform(
Text.ToList([Text]),
(t) =>
let
UL = {"A" .. "Z", "A" .. "Z", "a" .. "z", "a" .. "z"},
Nc = {"0" .. "9", "0" .. "9"},
U = List.PositionOf(UL, t),
N = List.PositionOf(Nc, t)
in
if U >= 0 then
UL{U + Number.Mod([Shift], 26)}
else if N >= 0 then
Nc{N + Number.Mod([Shift], 10)}
else
t
)
)
)
in
RsPower Query solution 2 for Caesar Cipher Multi Shift, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Solucion = Table.AddColumn(
Source,
"Expected Answer",
each
let
a = _,
b = Number.Mod(a[Shift], 26),
y = Number.Mod(a[Shift], 10),
c = {"A" .. "Z"},
d = {"a" .. "z"},
z = {"0" .. "9"},
e = Text.ToList(a[Text]),
f = Text.Combine(
List.Transform(
e,
each
if List.Contains(c, _) then
c{Number.Mod(List.PositionOf(c, _) + b, 26)}
else if not List.Contains(d, _) and not List.Contains(z, _) then
_
else if List.Contains(z, _) then
z{Number.Mod(List.PositionOf(z, _) + y, 10)}
else
d{Number.Mod(List.PositionOf(d, _) + b, 26)}
),
""
)
in
f
)[[Expected Answer]]
in
SolucionPower Query solution 3 for Caesar Cipher Multi Shift, proposed by Matthias Friedmann:
let
Source = Excel.CurrentWorkbook(){[Name = "CharacterShift"]}[Content],
#"Added Custom" = Table.AddColumn(
Source,
"Result",
each [
a = [Shift],
b = List.Transform(
Text.ToList([Text]),
each
if List.Contains({"0" .. "9"}, _) then
{"0" .. "9"}{Number.Mod(List.PositionOf({"0" .. "9"}, _) + a, 10)}
else if List.Contains({"A" .. "Z"}, _) then
{"A" .. "Z"}{Number.Mod(List.PositionOf({"A" .. "Z"}, _) + a, 26)}
else if List.Contains({"a" .. "z"}, _) then
{"a" .. "z"}{Number.Mod(List.PositionOf({"a" .. "z"}, _) + a, 26)}
else
_
),
c = Text.Combine(b)
][c]
)[[Result]]
in
#"Added Custom"Power Query solution 4 for Caesar Cipher Multi Shift, proposed by Krzysztof Kominiak:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Result = Table.AddColumn(
Source,
"Expected Answer",
each [
a = {"A" .. "Z"} & {"a" .. "z"} & {"0" .. "9"},
sl = Number.Mod([Shift], 26),
sn = Number.Mod([Shift], 10),
b = List.Range(List.Skip({"A" .. "Z", "A" .. "Z"}, sl), 0, 26)
& List.Range(List.Skip({"a" .. "z", "a" .. "z"}, sl), 0, 26)
& List.Range(List.Skip({"0" .. "9", "0" .. "9"}, sn), 0, 10),
d = Text.ToList([Text]),
e = Text.Combine(
List.Transform(
d,
each
if List.Contains(a, _) then
Table.SelectRows(Table.FromColumns({a, b}, {"Old", "New"}), (x) => _ = x[Old])[New]{0}
else
_
)
)
][e]
)
in
ResultPower Query solution 5 for Caesar Cipher Multi Shift, proposed by Thomas DUCROQUETZ:
let
Source = YourRawData,
ModifType = Table.TransformColumnTypes(Source, {{"Text", type text}, {"Shift", Int64.Type}}),
LettersLower = {"a" .. "z"},
LettersUpper = {"A" .. "Z"},
Numbers = {"0" .. "9"},
fnShiftCharacter = (carac as text, shift as number) as text =>
let
ListToConsider =
if List.Contains(LettersLower, carac) then
LettersLower
else if List.Contains(LettersUpper, carac) then
LettersUpper
else if List.Contains(Numbers, carac) then
Numbers
else
{},
PositionOfCharacter = List.PositionOf(ListToConsider, carac),
ShiftedPos =
let
ListLength = List.Count(ListToConsider)
in
Number.Mod(PositionOfCharacter + shift, ListLength)
in
try ListToConsider{ShiftedPos} otherwise carac,
Answer = Table.AddColumn(
ModifType,
"Expected Answer",
each
let
shift = [Shift]
in
Text.Combine(List.Transform(Text.ToList([Text]), each fnShiftCharacter(_, shift))),
type text
)
in
AnswerSolving the challenge of Caesar Cipher Multi Shift with Excel
Excel solution 1 for Caesar Cipher Multi Shift, proposed by Bo Rydobon 🇹🇭:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
z,
s,
LET(
x,
{0,
48,
58,
65,
91,
97,
123},
y,
{0,
10,
0,
26,
0,
26,
0},
c,
CODE(
MID(
z,
SEQUENCE(
LEN(
z
)
),
1
)
),
a,
LOOKUP(
c,
x
),
b,
LOOKUP(
c,
x,
y
),
CONCAT(
CHAR(
IF(
b,
MOD(
c+s-a-b,
b
)+a,
c
)
)
)
)
)
)Excel solution 2 for Caesar Cipher Multi Shift, proposed by Bo Rydobon 🇹🇭:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
z,
s,
LET(
m,
MID(
z,
SEQUENCE(
50
),
1
),
CONCAT(
IFERROR(
IFNA(
CHAR(
MOD(
XMATCH(
m,
CHAR(
SEQUENCE(
26
)+64
)
)+s-1,
26
)+65+32*EXACT(
LOWER(
m
),
m
)
),
MOD(
m+s,
10
)
),
m
)
)
)
)
)Excel solution 3 for Caesar Cipher Multi Shift, proposed by Rick Rothstein:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
t,
s,
LET(
d,
1234567890,
u,
"ABCDEFGHIJKLMNOPQRSTUVWXYZ",
l,
LOWER(
u
),
a,
u&u&l&l&d&d,
c,
MID(
t,
SEQUENCE(
LEN(
t
)
),
1
),
CONCAT(
IFERROR(
MID(
a,
FIND(
c,
a
)+MOD(
s,
IF(
ISNUMBER(
0+c
),
10,
26
)
),
1
),
c
)
)
)
)
)Excel solution 4 for Caesar Cipher Multi Shift, proposed by John V.:
=MAP(A2:A10,
B2:B10,
LAMBDA(x,
y,
LET(c,
CODE(
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)
),
b,
{0;48;58;65;91;97;123},
l,
LOOKUP(
c,
b
),
CONCAT(CHAR(IF(ISODD(
MATCH(
c,
b
)
),
c,
l+MOD(c+y-l,
26-16*(l=48))))))))Excel solution 5 for Caesar Cipher Multi Shift, proposed by محمد حلمي:
=MAP(A2:A10,
B2:B10,
LAMBDA(a,
b,
LET(
a,
CODE(
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
)
),
CONCAT(CHAR(
IFS(
(a>64)*(a<91),
MOD(
a+b-65,
26
)+65,
(a>96)*(a<123),
MOD(
a+b-97,
26
)+97,
(a>47)*(a<58),
MOD(
a+b-48,
10
)+48,
1,
a))))))Excel solution 6 for Caesar Cipher Multi Shift, proposed by Julian Poeltl:
=MAP(A2:A10,
B2:B10,
LAMBDA(T,
S,
LET(C,
CODE(
MID(
T,
SEQUENCE(
LEN(
T
)
),
1
)
),
CONCAT(CHAR(IFS((C>47)*(C<58),
MOD(
C-48+S,
10
)+48,
(C>64)*(C<91),
MOD(
C-65+S,
26
)+65,
(C>96)*(C<123),
MOD(
C-97+S,
26
)+97,
1,
C))))))Excel solution 7 for Caesar Cipher Multi Shift, proposed by Aditya Kumar Darak 🇮🇳:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
a,
b,
LET(
s,
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
cd,
CODE(
s
),
e,
LAMBDA(
x,
y,
z,
LET(
s,
SEQUENCE(
x,
,
y
),
r,
IFERROR(
INDEX(
s,
MOD(
XMATCH(
z,
s
) + b - 1,
x
) + 1
),
z
),
r
)
),
c1,
e(
26,
65,
cd
),
c2,
e(
26,
97,
c1
),
c3,
e(
10,
48,
c2
),
r,
CONCAT(
CHAR(
c3
)
),
r
)
)
)Excel solution 8 for Caesar Cipher Multi Shift, 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,
MAP(
LOWER(
A
),
LAMBDA(
x,
IF(
ISNUMBER(
x*1
),
MOD(
x+w,
10
),
IF(
AND(
CODE(
x
)>96,
CODE(
x
)<122
),
CHAR(
MOD(
CODE(
x
)-97+w,
26
)+97
),
x
)
)
)
),
CONCAT(
MAP(
SEQUENCE(
ROWS(
D
)
),
LAMBDA(
x,
IF(
INDEX(
B,
x
),
UPPER(
INDEX(
D,
x
)
),
INDEX(
D,
x
)
)
)
)
)
)
)
)Excel solution 9 for Caesar Cipher Multi Shift, proposed by Stefan Olsson:
=LAMBDA(
text,
shift,
az,
num,
MAP(
text,
shift,
LAMBDA(
t,
s,
REDUCE(
"",
REGEXEXTRACT(
t&"",
REPT(
"(.)",
LEN(
t
)
)
),
LAMBDA(
c,
a,
c&
IFS(
REGEXMATCH(
a&"",
"[^A-Za-z0-9]"
),
a,
REGEXMATCH(
a&"",
"[0-9]"
),
MID(
num&num,
10+RIGHT(
s+a,
1
),
1
),
REGEXMATCH(
a,
"[a-z]"
),
MID(
az&az,
26+MOD(
s+FIND(
a,
az
),
26
),
1
),
TRUE,
MID(
UPPER(
az&az
),
26+MOD(
s+FIND(
a,
UPPER(
az
)
),
26
),
1
)
)
)
)
)
)
)(A2:A11,
B2:B11,
"abcdefghijklmnopqrstuvwxyz",
"1234567890")Excel solution 10 for Caesar Cipher Multi Shift, proposed by Abhishek Kumar Jain:
=MAP(A2:A10,
B2:B10,
LAMBDA(a,
b,
LET(
c,
CODE(
MID(
a,
SEQUENCE(
,
LEN(
a
)
),
1
)
),
d,
c+MOD(
b,
26
),
e,
c+MOD(
b,
10
),
f,
IFS((c>=65)*(c<=90),
IF(
d>90,
65+d-90-1,
d
),
(c>=97)*(c<=122),
IF(
d>122,
97+d-122-1,
d
),
(c>=48)*(c<=57),
IF(
e>57,
48+e-57-1,
e
),
TRUE,
c),
TEXTJOIN(
"",
FALSE,
CHAR(
f
)
))))Solving the challenge of Caesar Cipher Multi Shift with SQL
SQL solution 1 for Caesar Cipher Multi Shift, proposed by Zoran Milokanović:
WITH -- Microsoft SQL Server 2019
ALPHABET
AS
(
SELECT CHAR(ASCII('A')) AS CHR, '0123456789' AS NUM
UNION ALL
SELECT CHAR(ASCII(CHR) + 1) AS CHR, A.NUM
FROM ALPHABET A
WHERE
ASCII(A.CHR) <> ASCII('Z')
),
CONVERTER
AS
(
SELECT
STRING_AGG(A.CHR, '') AS UPPER
,LOWER(STRING_AGG(A.CHR, '')) AS LOWER
,MIN(A.NUM) NUM
FROM ALPHABET A
)
SELECT
D.TEXT
,D.SHIFT
,TRANSLATE(
TRANSLATE(
TRANSLATE(
D.TEXT,
C.UPPER, SUBSTRING(C.UPPER + C.UPPER, 1 + D.SHIFT % LEN(C.UPPER), LEN(C.UPPER))
),
C.LOWER, SUBSTRING(C.LOWER + C.LOWER, 1 + D.SHIFT % LEN(C.LOWER), LEN(C.LOWER))
),
C.NUM, SUBSTRING(C.NUM + C.NUM, 1 + D.SHIFT % LEN(C.NUM), LEN(C.NUM))
) AS EXPECTED_ANSWER
FROM DATA D
CROSS JOIN CONVERTER C
ORDER BY
D.SHIFT
;
&&