Decrypt the given Encrypted text. Encryption logic is below which you would need to reverse. Caesar’s Cipher variation (Case sensitive) 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”.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 480
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Decrypt Caesar Cipher Variation with Power Query
Power Query solution 1 for Decrypt Caesar Cipher Variation, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Ans = Table.AddColumn(
Source,
"Ans",
each Text.Combine(
List.Transform(
{0 .. Text.Length([Encrypted Text]) - 1},
(s) =>
let
c = Character.ToNumber(Text.Middle([Encrypted Text], s, 1))
in
Character.FromNumber(
Number.Mod(Number.Mod(c, 32) - s - [Shift] + 259, 26)
+ 1
+ Number.IntegerDivide(c, 32)
* 32
)
)
)
)
in
AnsPower Query solution 2 for Decrypt Caesar Cipher Variation, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Return = Table.AddColumn(
Source,
"Answer",
each [
L = Text.Lower([Encrypted Text]),
C = Text.Length(L) - 1,
T = List.Transform(
{0 .. C},
(f) =>
[
a = Text.At(L, f),
A = Text.At([Encrypted Text], f),
c = Character.ToNumber(a) - 97,
sw = Number.Mod(26 + c - Number.Mod([Shift], 26) - f, 26),
r = Character.FromNumber((if a = A then 97 else 65) + sw)
][r]
),
R = Text.Combine(T)
][R]
)
in
ReturnPower Query solution 3 for Decrypt Caesar Cipher Variation, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Sol = Table.AddColumn(Source, "Custom", (x)=>
let
a = Text.ToList(x[Encrypted Text]),
b = List.Transform(a, each Character.ToNumber(Text.Lower(_))-97),
c = List.Transform({0..List.Count(b)-1}, each
Character.FromNumber(Number.Mod(b{_}-_-Number.Mod(x[Shift],26)+26,26)+97)),
d = List.Transform({0..List.Count(b)-1}, each
if a{_}=Text.Upper(a{_}) then Text.Upper(c{_}) else c{_})
in Text.Combine(d))
in
Sol
Este código resuelve el ejemplo de War, shift 5.
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Sol = Table.AddColumn(Source, "Custom", (x)=>
let
a = Text.ToList(x[Encrypted Text]),
b = List.Transform(a, each Character.ToNumber(Text.Lower(_))),
c = List.Transform({0..List.Count(b)-1}, each Character.FromNumber(Number.Mod(b{_}+_+x[Shift]-97, 26)+97)),
d = List.Transform({0..List.Count(b)-1}, each if a{_}=Text.Upper(a{_})then Text.Upper(c{_}) else c{_})
in Text.Combine(d))
in
Sol
Power Query solution 4 for Decrypt Caesar Cipher Variation, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
f = (txt, shift) =>
let
a = List.Zip({Text.ToList(txt), {0 .. Text.Length(txt) - 1}}),
b = {"a" .. "z", "A" .. "Z"},
c = List.Accumulate(
a,
"",
(x, y) =>
let
num1 = Character.ToNumber(y{0}) - Number.Mod(y{1} + shift, 26),
char = Character.FromNumber(num1),
test1 = (y{0} = Text.Upper(y{0}))
and (char = Text.Upper(char))
and List.Contains(b, char),
test2 = (y{0} = Text.Lower(y{0}))
and (char = Text.Lower(char))
and List.Contains(b, char),
num2 = if test1 or test2 then num1 else num1 + 26
in
x & Character.FromNumber(num2)
)
in
c,
Result = Table.AddColumn(Source, "My Answer", each f([Encrypted Text], [Shift]))
in
ResultPower Query solution 5 for Decrypt Caesar Cipher Variation, proposed by Challa Sai Kumar Reddy:
let
f = (txt, shift) =>
let
a = List.Zip({Text.ToList(txt), {0 .. Text.Length(txt) - 1}}),
b = {"a" .. "z", "A" .. "Z"},
c = List.Accumulate(
a,
"",
(x, y) =>
let
num1 = Character.ToNumber(y{0}) - Number.Mod(y{1} + shift, 26),
char = Character.FromNumber(num1),
test1 = (y{0} = Text.Upper(y{0}))
and (char = Text.Upper(char))
and List.Contains(b, char),
test2 = (y{0} = Text.Lower(y{0}))
and (char = Text.Lower(char))
and List.Contains(b, char),
num2 = if test1 or test2 then num1 else num1 + 26
in
x & Character.FromNumber(num2)
)
in
c,
Result = f("Encrypted Text", 3)
in
ResultSolving the challenge of Decrypt Caesar Cipher Variation with Excel
Excel solution 1 for Decrypt Caesar Cipher Variation, proposed by Bo Rydobon 🇹🇭:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
a,
b,
LET(
s,
SEQUENCE(
LEN(
a
)
),
c,
CODE(
MID(
a,
s,
1
)
),
f,
FLOOR(
c,
32
),
CONCAT(
CHAR(
MOD(
c-f-s-b,
26
)+1+f
)
)
)
)
)Excel solution 2 for Decrypt Caesar Cipher Variation, proposed by Rick Rothstein:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
x,
y,
LET(
s,
SEQUENCE(
LEN(
x
)
),
m,
CODE(
MID(
x,
s,
1
)
),
a,
IF(
m>96,
97,
65
),
CONCAT(
CHAR(
a+MOD(
1+m-a-y-s,
26
)
)
)
)
)
)Excel solution 3 for Decrypt Caesar Cipher Variation, proposed by John V.:
=MAP(A2:A10,
B2:B10,
LAMBDA(a,
b,
LET(s,
SEQUENCE(
LEN(
a
)
),
c,
CODE(
MID(
a,
s,
1
)
),
d,
65+32*(c>90),
CONCAT(
CHAR(
MOD(
1+c-d-b-s,
26
)+d
)
))))Excel solution 4 for Decrypt Caesar Cipher Variation, proposed by محمد حلمي:
=MAP(A2:A10,
B2:B10,
LAMBDA(a,
b,
LET(s,
SEQUENCE(
LEN(
a
)
),
e,
CODE(
MID(
a,
s,
1
)
),
i,
MOD(
e-s-b+1,
26
),
CONCAT(CHAR(i+52+IF(e>90,
26+(i<19)*26,
(i<13)*26))))))Excel solution 5 for Decrypt Caesar Cipher Variation, proposed by Kris Jaganah:
=MAP(
A2:A10,
MOD(
B2:B10,
26
),
LAMBDA(
x,
y,
LET(
a,
SEQUENCE(
26,
,
65
),
b,
CHAR(
a
),
c,
a-64,
d,
TAKE(
c,
LEN(
x
)
),
e,
MID(
x,
d,
1
),
f,
XLOOKUP(
e,
b,
c
)-y-d+1,
g,
CHAR(
IF(
f<0,
26+f,
f
)+64
),
h,
CONCAT(
IF(
CODE(
e
)>90,
LOWER(
g
),
g
)
),
h
)
)
)Excel solution 6 for Decrypt Caesar Cipher Variation, proposed by Julian Poeltl:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
E,
S,
LET(
L,
LEN(
E
),
SQ,
-SEQUENCE(
L,
,
S,
1
),
C,
CODE(
MID(
E,
SEQUENCE(
L
),
1
)
),
X,
IF(
C>96,
97,
65
),
CONCAT(
CHAR(
MOD(
C-X+SQ,
26
)+X
)
)
)
)
)Excel solution 7 for Decrypt Caesar Cipher Variation, proposed by Timothée BLIOT:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
v,
w,
LET(
A,
SEQUENCE(
LEN(
v
)
),
B,
MID(
v,
A,
1
),
CONCAT(
MAP(
CHAR(
MAP(
CODE(
UPPER(
B
)
)-65,
A-1+w,
LAMBDA(
x,
y,
MOD(
x-y,
26
)
)
) +65
),
MAP(
B,
LAMBDA(
x,
EXACT(
x,
UPPER(
x
)
)
)
),
LAMBDA(
x,
y,
IF(
y,
x,
LOWER(
x
)
)
)
)
)
)
)
)Excel solution 8 for Decrypt Caesar Cipher Variation, proposed by Oscar Mendez Roca Farell:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
a,
b,
LET(
n,
96,
e,
SEQUENCE(
LEN(
a
)
),
w,
MID(
a,
e,
1
),
c,
CHAR(
n+MOD(
CODE(
LOWER(
w
)
)-b-e-n+1,
26
)
),
CONCAT(
IF(
CODE(
w
)Excel solution 9 for Decrypt Caesar Cipher Variation, proposed by Oscar Mendez Roca Farell:
=MAP(A2:A10,
B2:B10,
LAMBDA(a,
b,
LET(e,
SEQUENCE(
LEN(
a
)
),
w,
CODE(
MID(
a,
e,
1
)
),
x,
32*(2+(w>96)),
CONCAT(
CHAR(
MOD(
w-x-e-b+1,
26
)+x
)
))))Excel solution 10 for Decrypt Caesar Cipher Variation, proposed by Sunny Baggu:
=MAP(
A2:A10,
B2:B10,
LAMBDA(e,
f,
LET(
_l,
LEN(
e
),
_m,
MID(
e,
SEQUENCE(
_l
),
1
),
_s,
SEQUENCE(
_l,
,
f
),
_c,
CODE(
_m
),
CONCAT(
MAP(
_c,
_s,
LAMBDA(x,
y,
LET(
_st,
IF(
x < 97,
65,
97
),
_tbl,
((26 + _st) + SEQUENCE(
,
7
) * -26) + SEQUENCE(
26,
,
0
),
_v,
FILTER(CHAR(
SEQUENCE(
26,
,
_st
)
),
BYROW(_tbl = (x - y),
LAMBDA(
a,
OR(
a
)
))),
_v
)
)
)
)
)
)
)Excel solution 11 for Decrypt Caesar Cipher Variation, proposed by Anshu Bantra:
=MAP(A2:A10,
B2:B10,
LAMBDA(word,
shift,
LET(
ln_,
LEN(
word
),
seq_,
SEQUENCE(
ln_,
,
MOD(
shift,
26
)
),
start_,
MID(
word,
SEQUENCE(
ln_
),
1
),
code_,
CODE(
start_
),
ini_,
IF(
code_ > 96,
97,
65
),
char_,
(code_ - seq_),
TEXTJOIN(
"",
,
CHAR(IF(char_ < ini_,
(ini_ + 25) - ((ini_ - 1) - (char_)),
char_))
)
)))Excel solution 12 for Decrypt Caesar Cipher Variation, proposed by Bilal Mahmoud kh.:
=97,
IF(
a-c<97,
a-c+26,
a-c
),
a-c)),
d))))))Excel solution 13 for Decrypt Caesar Cipher Variation, proposed by Pieter de Bruijn:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
e,
s,
LET(
q,
SEQUENCE(
LEN(
e
)
),
c,
CODE(
MID(
e,
q,
1
)
& ),
i,
IF(
c>96,
97,
65
),
CONCAT(
CHAR(
MOD(
c-i-q+1-s,
26
)+i
)
)
)
)
)Excel solution 14 for Decrypt Caesar Cipher Variation, proposed by Sandeep Marwal:
=MAP(
A2:A10,
LAMBDA(
a,
LET(
split,
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
lur,
CODE(
VSTACK(
CHAR(
SEQUENCE(
26,
,
97
)
),
CHAR(
SEQUENCE(
26,
,
97
)
),
CHAR(
SEQUENCE(
26,
,
65
)
),
CHAR(
SEQUENCE(
26,
,
65
)
)
)
),
output,
REDUCE(
"",
split,
LAMBDA(
b,
d,
b&CHAR(
INDEX(
lur,
XMATCH(
CODE(
d
),
lur,
0,
-1
)-MOD(
OFFSET(
a,
,
1
),
26
)-LEN(
b
)
)
)
)
),
output
)
)
)Excel solution 15 for Decrypt Caesar Cipher Variation, proposed by Ricardo Alexis Domínguez Hernández:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
a,
b,
CONCAT(
LET(
c,
CODE(
MID(
a,
SEQUENCE(
,
LEN(
a
)
),
1
)
),
CHAR(
LET(
a,
LET(
y,
c-SEQUENCE(
,
LEN(
a
),
b
),
IF(
c<97,
MOD(
64-y,
26
),
MOD(
96-y,
26
)
)
),
IF(
c<97,
90-a,
122-a
)
)
)
)
)
)
)
This was a nice one,
it reminds me of the Passing Notes case in the MEWC 2022. It was a similar case where if I remember correctly the letters were shifted by (n*original shift)Excel solution 16 for Decrypt Caesar Cipher Variation, proposed by Tyler Cameron:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
x,
y,
LET(
a,
LEN(
x
),
z,
CODE(
MID(
x,
SEQUENCE(
,
a
),
1
)
),
v,
SEQUENCE(
,
a,
y-26*INT(
y/26
)
),
CONCAT(
CHAR(
IF(
z>96,
IF(
z-v<97,
z-v+26,
z-v
),
IF(
z-v<65,
z-v+26,
z-v
)
)
)
)
)
)
)Solving the challenge of Decrypt Caesar Cipher Variation with Python
Python solution 1 for Decrypt Caesar Cipher Variation, proposed by Konrad Gryczan, PhD:
import pandas as pd
import numpy as np
path = "480 Caesar's Cipher_Decrypter.xlsx"
input = pd.read_excel(path, usecols="A:B")
test = pd.read_excel(path, usecols="C")
def decrypt_caesar(encrypted_text, shift):
def shift_char(char, shift_value):
if char.isalpha():
base = 97 if char.islower() else 65
char_val = ord(char) - base
shifted_val = (char_val - shift_value) % 26
return chr(shifted_val + base)
else:
return char
decrypt_char = np.vectorize(shift_char)
decrypted_text = ''.join(decrypt_char(list(encrypted_text), np.arange(len(encrypted_text)) + shift))
return decrypted_text
result = input.copy()
result['Answer Expected'] = result.apply(lambda row: decrypt_caesar(row['Encrypted Text'], row['Shift']), axis=1)
print(result['Answer Expected'].equals(test['Answer Expected'])) # True
Solving the challenge of Decrypt Caesar Cipher Variation with Python in Excel
Python in Excel solution 1 for Decrypt Caesar Cipher Variation, proposed by Abdallah Ally:
import pandas as pd
def decriptor(text, shift):
decripted = ''
for key, char in enumerate(text):
num = ord(char) - (shift + key) % 26
num = (
num if char.isupper() == chr(num).isupper()
else ord(char) + 26 - (shift + key) % 26
)
decripted += chr(num)
return decripted
file_path = "Caesar's Cipher_Decrypter.xlsx"
df = pd.read_excel(file_path)
# Perform data wrangling
df['My Answer'] = df.apply(lambda x: decriptor(*x[: 2]), axis=1)
df['check'] = df['Answer Expected'] == df['My Answer']
df
Solving the challenge of Decrypt Caesar Cipher Variation with R
R solution 1 for Decrypt Caesar Cipher Variation, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/480 Caesar's Cipher_Decrypter.xlsx"
input = read_excel(path, range = "A1:B10")
test = read_excel(path, range = "C1:C10")
decrypt_caesar <- function(encrypted_text, shift) {
shift_char <- function(char, shift_value) {
if (char %in% letters) {
base <- 97
char_val <- utf8ToInt(char) - base
shifted_val <- (char_val - shift_value) %% 26
intToUtf8(shifted_val + base)
} else if (char %in% LETTERS) {
base <- 65
char_val <- utf8ToInt(char) - base
shifted_val <- (char_val - shift_value) %% 26
intToUtf8(shifted_val + base)
} else {
char
}
}
decrypt_char <- Vectorize(shift_char, "char")
decrypted_text <- map2_chr(str_split(encrypted_text, "")[[1]], 0:(nchar(encrypted_text) - 1),
~ decrypt_char(.x, shift + .y))
paste0(decrypted_text, collapse = "")
}
result = input %>%
mutate(`Answer Expected` = map2_chr(`Encrypted Text`, Shift, decrypt_caesar)) %>%
select(`Answer Expected`)
identical(result, test)
# [1] TRUE
R solution 2 for Decrypt Caesar Cipher Variation, proposed by Anil Kumar Goyal:
library(readxl)
library(tidyverse)
df <- read_excel("Excel/Caesar's Cipher_Decrypter.xlsx") %>%
janitor::clean_names()
rev_ceasar <- function(word, shift){
unlist(str_split(word, "")) %>%
imap_chr(~ if(str_to_upper(.x) == .x){
LETTERS[1 + (match(.x, LETTERS) - .y - shift) %% 26]
} else {
letters[1 + (match(.x, letters) - .y - shift) %% 26]
} ) %>%
str_flatten()
}
df %>%
mutate(my_answer = map2_chr(encrypted_text, shift, rev_ceasar)) %>%
mutate(CHECK = my_answer == answer_expected)
&&