Mirror Cipher with Caesar’s Shift – Reverse the words and then reverse the alphabets within the words. Then apply Caesar’s shift with the shift value given to encrypt the sentences. Ex. bomb diffused with shift 5 reverse words – diffused bomb reverse alphabets – desuffid bmob Apply shift of 5 – ijxzkkni grtg
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 407
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Reverse, Cipher and Shift Text with Power Query
Power Query solution 1 for Reverse, Cipher and Shift Text, proposed by John V.:
let
S = Excel.CurrentWorkbook(){0}[Content],
R = Table.AddColumn(S, "R", each
[s = [Shift], a = Text.ToList(Text.Reverse([Plain Text])),
b = Text.Combine(List.Transform(a, each if _ < "a" then _ else Character.FromNumber(97 + Number.Mod(s + Character.ToNumber(_) - 97, 26))))][b]
)[[R]]
in
R
Blessings!
Power Query solution 2 for Reverse, Cipher and Shift Text, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.AddColumn(
Source,
"Answer",
(x) =>
let
a = List.Transform(Text.Split(x[Plain Text], " "), Text.ToList),
b = List.Transform(
a,
each Text.Reverse(
Text.Combine(
List.Transform(
_,
(y) =>
Character.FromNumber(Number.Mod(Character.ToNumber(y) - 97 + x[Shift], 26) + 97)
)
)
)
)
in
Text.Combine(List.Reverse(b), " ")
)[[Answer]]
in
SolPower Query solution 3 for Reverse, Cipher and Shift Text, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
res = Table.AddColumn(
Fonte,
"Personalizar",
each Text.Replace(
Text.Replace(
Text.Combine(
List.Transform(
Text.ToList(Text.Reverse([Plain Text])),
(x) =>
Text.Select(
Character.FromNumber(
if Character.ToNumber(x) + [Shift] > 122 then
(Character.ToNumber(x) + [Shift] - 123) + 97
else
Character.ToNumber(x) + [Shift]
),
{"a" .. "z"}
)
),
","
),
",,",
" "
),
",",
""
)
)
in
resPower Query solution 4 for Reverse, Cipher and Shift Text, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Fx = (x, y) =>
let
t = x,
s = y,
a = List.Reverse(Text.Split(t, " ")),
b = Text.ToList(Text.Combine(List.Transform(a, each Text.Reverse(_)), " ")),
c = {"a" .. "z"},
d = List.Skip(c, s),
e = d & List.Difference(c, d),
f = List.Zip({c, List.Positions(c)}),
g = List.ReplaceMatchingItems(b, f),
h = List.Zip({List.Positions(e), e}),
i = Text.Combine(List.ReplaceMatchingItems(g, h))
in
i,
Sol = Table.AddColumn(S, "Answer Expected", each Fx([Plain Text], [Shift]))
in
SolPower Query solution 5 for Reverse, Cipher and Shift Text, proposed by Glyn Willis:
let
fxShift = (String as text, Shift as number) as text =>
let
alpha = {"a" .. "z"},
rev = List.Transform(
List.Reverse(Text.Split(Text.From(String), " ")),
(x) =>
List.Transform(
List.Reverse(Text.ToList(x)),
(y) => alpha{Number.Mod(List.PositionOf(alpha, y) + Int64.From(Shift), 26)}?
)
),
comb = Text.Combine(List.Transform(rev, (x) => Text.Combine(x)), " ")
in
comb,
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(
Source,
{{"Plain Text", type text}, {"Shift", Int64.Type}, {"Answer Expected", type text}}
),
#"Added Custom" = Table.AddColumn(
#"Changed Type",
"Custom",
each fxShift([Plain Text], [Shift]),
type text
)
in
#"Added Custom"Solving the challenge of Reverse, Cipher and Shift Text with Excel
Excel solution 1 for Reverse, Cipher and Shift Text, proposed by Bo Rydobon 🇹🇭:
=MAP(A2:A10,B2:B10,LAMBDA(a,n,LET(m,MID(a,51-SEQUENCE(50),1),CONCAT(IF(m>"9",CHAR(MOD(CODE(m)-97+n,26)+97),m)))))Excel solution 2 for Reverse, Cipher and Shift Text, proposed by John V.:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
a,
b,
LET(
c,
MID(
a,
31-ROW(
1:30
),
1
),
CONCAT(
IF(
c<"a",
c,
CHAR(
97+MOD(
b+CODE(
c
)-97,
26
)
)
)
)
)
)
)Excel solution 3 for Reverse, Cipher and Shift Text, proposed by محمد حلمي:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
a,
b,
LET(
i,
LEN(
a
),
e,
CODE(
MID(
a,
i+1-SEQUENCE(
i
),
1
)
),
CONCAT(
CHAR(
IF(
e<90,
e,
MOD(
e-123+b,
26
)+97
)
)
)
)
)
)Excel solution 4 for Reverse, Cipher and Shift Text, proposed by Kris Jaganah:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
x,
y,
LET(
a,
CODE(
MID(
x,
SEQUENCE(
LEN(
x
),
,
LEN(
x
),
-1
),
1
)
)+y,
b,
IF(
a>122,
a-26,
a
),
CONCAT(
IF(
b=32+y,
" ",
CHAR(
b
)
)
)
)
)
)Excel solution 5 for Reverse, Cipher and Shift Text, proposed by Julian Poeltl:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
PT,
SH,
LET(
LPT,
LEN(
PT
),
RES,
CONCAT(
LOWER(
IFERROR(
E_LetterOffsetter_Chiffre(
MID(
PT,
SEQUENCE(
LPT,
,
LPT,
-1
),
1
),
SH
),
" "
)
)
),
RES
)
)
)
I've used my pre-programmed Lambda E_LetterOffsetter_Chiffre:
=LAMBDA(Letter,
OffsetValue,
LET(VL,
XMATCH(
Letter,
E_AtoZVertical()
),
INDEX(E_AtoZVertical(),
L_Rest_Remainder_MOD_Basedon1((VL+OffsetValue),
26))))
While E_AtoZVertical() is: =LAMBDA(
CHAR(
64+SEQUENCE(
26
)
)
)
and L_Rest_Remainder_MOD_Basedon1 is: =LAMBDA(
Number;Divider;MOD(
Number-1;Divider
)+1
)Excel solution 6 for Reverse, Cipher and Shift Text, proposed by Julian Poeltl:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
PT,
SH,
LET(
LPT,
LEN(
PT
),
CD,
CODE(
MID(
PT,
SEQUENCE(
LPT,
,
LPT,
-1
),
1
)
)+SH,
CONCAT(
CHAR(
IF(
CD=32+SH,
32,
IF(
CD>CODE(
"a"
)+25,
CD-26,
CD
)
)
)
)
)
)
)Excel solution 7 for Reverse, Cipher and Shift Text, proposed by Timothée BLIOT:
=LET(
A,
TEXTSPLIT(
A2,
,
" "
),
B,
SORTBY(
A,
SEQUENCE(
ROWS(
A
)
),
-1
),
C,
MAP(
B,
LAMBDA(
x,
CONCAT(
MID(
x,
SEQUENCE(
LEN(
x
),
,
LEN(
x
),
-1
),
1
)
)
)
),
TEXTJOIN(
" ",
,
MAP(
C,
LAMBDA(
x,
CONCAT(
CHAR(
MOD(
CODE(
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)
)-97+B2,
26
)+97
)
)
)
)
)
)Excel solution 8 for Reverse, Cipher and Shift Text, proposed by Sunny Baggu:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
a,
b,
LET(
_ts,
TEXTSPLIT(
a,
,
" "
),
_s,
SORTBY(
_ts,
SEQUENCE(
ROWS(
_ts
)
),
-1
),
TEXTJOIN(
" ",
,
MAP(
_s,
LAMBDA(
d,
LET(
_v,
CODE(
MID(
d,
LEN(
d
) + 1 - SEQUENCE(
LEN(
d
)
),
1
)
) + b,
CONCAT(
CHAR(
IF(
_v > 122,
_v - 122 + 96,
_v
)
)
)
)
)
)
)
)
)
)Excel solution 9 for Reverse, Cipher and Shift Text, proposed by Asheesh Pahwa:
=LET(
pt,
F4:F7,
st,
G4:G7,
MAP(
pt,
st,
LAMBDA(
x,
y,
LET(
m,
MID(
x,
SEQUENCE(
LEN(
x
),
,
LEN(
x
),
-1
),
1
),
c,
CODE(
m
),
f,
IF(
c=32,
32,
c+y
),
k,
IF(
f<=122,
f,
MOD(
f,
122
)+96
),
CONCAT(
CHAR(
k
)
)
)
)
)
)Excel solution 10 for Reverse, Cipher and Shift Text, proposed by Charles Roldan:
=MAP(A2:A10,
B2:B10,
LAMBDA(
g,
g(
g
)
)(LAMBDA(g,
LAMBDA(x,
n,
IF(LEN(
x
),
g(
g
)(REPLACE(
x,
1,
1,
),
n) & LAMBDA(
x,
IF(
x = " ",
" ",
CHAR(
97 + MOD(
CODE(
x
) - 97 + n,
26
)
)
)
)(LEFT(
x
)),
)))))Excel solution 11 for Reverse, Cipher and Shift Text, proposed by Charles Roldan:
=LET(
Caesar,
LAMBDA(
n,
LAMBDA(
a,
IF(
a = " ",
" ",
CHAR(
97 + MOD(
CODE(
a
) - 97 + n,
26
)
& )
)
)
),
Recurse,
LAMBDA(
f,
f(
f
)
),
Cipher,
LAMBDA(f,
LAMBDA(Text,
Shift,
IF(LEN(
Text
),
f(
f
)(REPLACE(
Text,
1,
1,
),
Shift) & Caesar(
Shift
)(LEFT(
Text
)),
))),
MAP(
A2:A10,
B2:B10,
Recurse(
Cipher
)
)
)Excel solution 12 for Reverse, Cipher and Shift Text, proposed by Andy Heybruch:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
_text,
_shift,
CONCAT(
UNICHAR(
SCAN(
0,
UNICODE(
MID(
_text,
SEQUENCE(
LEN(
_text
),
,
LEN(
_text
),
-1
),
1
)
),
LAMBDA(
a,
v,
IF(
v<97,
v,
MOD(
v+_shift-97,
26
)+97
)
)
)
)
)
)
)Excel solution 13 for Reverse, Cipher and Shift Text, proposed by Bilal Mahmoud kh.:
=MAP(MAP(A2:A10,B2:B10,LAMBDA(x,y,LET(x,TEXTSPLIT(x," "),b,INDEX(x,0,SEQUENCE(COUNTA(x),,COUNTA(x),-1)),c,MAP(b,LAMBDA(x,CONCAT(MID(x,SEQUENCE(LEN(x),,LEN(x),-1),1)))),d,TEXTJOIN(" ",TRUE,MAP(c,LAMBDA(x,CONCAT(CHAR(CODE(MID(x,SEQUENCE(LEN(x)),1))+y))))),d))),LAMBDA(x,CONCAT(LET(a,CODE(MID(x,SEQUENCE(LEN(x)),1)),b,IF(a>122,CHAR(a-26),CHAR(a)),b))))Excel solution 14 for Reverse, Cipher and Shift Text, proposed by Ernesto Vega Castillo:
=MAP(A2:A10,
B2:B10,
LAMBDA(str,
sht,
LET(lgr,
LEN(
str
),
ptx,
CODE(
MID(
str,
SEQUENCE(
lgr,
,
lgr,
-1
),
1
)
),
TEXTJOIN("",
1,
(CHAR(
IF(
ptx<90,
ptx,
MOD(
ptx-123+sht,
26
)+97
)
))))))Excel solution 15 for Reverse, Cipher and Shift Text, proposed by Tyler Cameron:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
y,
z,
LET(
a,
TEXTSPLIT(
y,
" "
),
b,
COUNTA(
a
),
TEXTJOIN(
" ",
TRUE,
MAP(
INDEX(
a,
,
SEQUENCE(
b,
,
b,
-1
)
),
LAMBDA(
x,
CONCAT(
IF(
(
CODE(
MID(
x,
SEQUENCE(
LEN(
x
),
,
LEN(
x
),
-1
),
1
)
)+z
)-96<27,
CHAR(
CODE(
MID(
x,
SEQUENCE(
LEN(
x
),
,
LEN(
x
),
-1
),
1
)
)+z
),
CHAR(
(
CODE(
MID(
x,
SEQUENCE(
LEN(
x
),
,
LEN(
x
),
-1
),
1
)
)+z
)-26
)
)
)
)
)
)
)
)
)Solving the challenge of Reverse, Cipher and Shift Text with Python
Python solution 1 for Reverse, Cipher and Shift Text, proposed by Cristobal Salcedo Beltran:
https://github.com/cristobalsalcedo90/BI_Challenges
code:
from pyspark.sql import SparkSession
from pyspark.sql.functions import udf, col
from pyspark.sql.types import StringType
import pandas as pd
spark = SparkSession.builder.appName("Excel to Spark DF").getOrCreate()
file_path = "/lakehouse/default/Files/Challenge/Excel_Challenge_407 - Mirror Cipher.xlsx"
pandas_df = pd.read_excel(file_path, usecols="A:B")
spark_df = spark.createDataFrame(pandas_df)
def mirror_cipher_caesar_shift(plaintext, shift):
words_reversed = plaintext.split()[::-1]
encrypted_sentence = ''.join(
chr(((ord(char) - 65 + shift) % 26) + 65) if char.isupper() else
chr(((ord(char) - 97 + shift) % 26) + 97) if char.islower() else char
for char in mirrored_sentence
)
return encrypted_sentence
encrypt_udf = udf(mirror_cipher_caesar_shift, StringType())
df_result = spark_df.withColumn("Answer Expected", encrypt_udf(col("Plain Text"), col("Shift")))
display(df_result)
Solving the challenge of Reverse, Cipher and Shift Text with R
R solution 1 for Reverse, Cipher and Shift Text, proposed by Konrad Gryczan, PhD:
library(tidyverse)
input = tibble(plain_text = c("battle won", "spy on prowl",
"microsoft excel", "linkedin is great"),
shift = c(2,10,8,5))
test = tibble(answer_expected = c("pqy gnvvcd", "vgybz xy izc",
"tmkfm bnwawzkqu", "yfjwl xn snijpsnq"))
code = function(text, shift) {
keycode = c(letters[(26-shift+1):26],letters[1:(26-shift)])
keytable = tibble(letters = letters, code = keycode)
chars = str_split(text, "")[[1]] %>%
rev()
tab = tibble(text = chars) %>%
left_join(keytable, by = c("text" = "code")) %>%
mutate(letters = if_else(is.na(letters), " ", letters)) %>%
select(letters) %>%
pull() %>%
str_c(collapse = "")
return(tab)
}
result = input %>%
mutate(answer_expected = map2_chr(plain_text, shift, code))
identical(result$answer_expected, test$answer_expected)
# [1] TRUE
&&