Polybius Cipher Decrypter – Decrypt the text in column I as per grid in A2:G8. Encryption was done as per below – You need to assign a number to a character as row 1 cell concatenated with column 1 cell of the grid (highlighted in Orange) Hence, A=11, D=14, N = 32, V = 44, 0 = 53, 8 = 65….. If a character is not found in the grid, the same should not be encrypted. For decryption, above logic need to be reversed.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 243
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Decrypt Polybius Cipher Grid with Power Query
Power Query solution 1 for Decrypt Polybius Cipher Grid, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Grid = Table.Buffer(
Table.TransformColumns(
Excel.CurrentWorkbook(){[Name = "Grid"]}[Content],
{},
each Text.Lower(Text.From(_))
)
),
Rec = (a, b) =>
let
l = Text.Start(a, 1),
c = Table.Column(Grid, Text.At(a, 1)){Number.From(l) - 1}
in
if a = "" then
b
else
@Rec(Text.Middle(a, 2 - (try Number.From(c = "") otherwise 1), 99), b & (try c otherwise l)),
Ans = Table.TransformRows(Source, each Rec([Encrypted Text], ""))
in
AnsPower Query solution 2 for Decrypt Polybius Cipher Grid, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Grid = Table.Buffer(Excel.CurrentWorkbook(){[Name = "Grid"]}[Content]),
Ans = Table.TransformRows(
Source,
each
let
a = [Encrypted Text],
d = List.Select(Text.SplitAny(a, "123456"), each _ > ""),
b = List.Transform(
List.Select(Text.SplitAny(a, Text.Combine(d)), each _ > ""),
each Text.Combine(
List.Transform(
List.Split(Text.ToList(_), 2),
each Text.From(Table.Column(Grid, _{1}){Number.From(_{0}) - 1})
)
)
)
in
Text.Lower(Text.Combine(List.Transform(List.Zip({b, d}), each Text.Combine(_))))
)
in
AnsPower Query solution 3 for Decrypt Polybius Cipher Grid, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
Chr = {"a" .. "z", "0" .. "9"},
Num = List.Transform(
List.Positions(Chr),
each Text.From(Number.IntegerDivide(_, 6) + 1) & Text.From(Number.Mod(_, 6) + 1)
),
S = Table.TransformRows(
Source,
(r) =>
List.Last(
List.Generate(
() => [P = 0, T = ""],
each [P] <= Text.Length(r[Encrypted Text]),
each
let
s = Text.Middle(r[Encrypted Text], [P], 2),
p = List.PositionOf(Num, s)
in
[
P = [P] + 1 + Number.From(p > - 1),
T = [T] & (if p = - 1 then Text.Start(s, 1) else Chr{p})
],
each [T]
)
)
)
in
SPower Query solution 4 for Decrypt Polybius Cipher Grid, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Table1,
UnPivot = List.Transform(
List.Zip(
Table.ToColumns(
Table.AddColumn(
Table.UnpivotOtherColumns(Source, {"Column1"}, "Att", "Val"),
"New",
each Text.Combine({Text.From([Column1])} & {[Att]})
)[[New], [Val]]
)
),
each {_{0}} & {Text.From(_{1})}
),
Sol = Table.AddColumn(
Table2,
"Answer",
each Text.Combine(
let
a = Splitter.SplitTextByCharacterTransition({"@", " ", ".", ",", "-"}, {"0" .. "9"})(
[Encrypted Text]
),
b = List.Transform(
a,
each Text.Lower(
Text.Combine(
List.ReplaceMatchingItems(
List.Transform(List.Split(Text.ToList(_), 2), (x) => Text.Combine(x)),
UnPivot
)
)
)
)
in
b
)
)
in
SolPower Query solution 5 for Decrypt Polybius Cipher Grid, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Zip = List.Zip(
Table.ToColumns(
Table.AddColumn(
Table.UnpivotOtherColumns(Source, {"Column1"}, "Att", "Val"),
"Custom",
each Text.From([Column1]) & [Att]
)[[Custom], [Val]]
)
),
Sol = Table.TransformColumns(
Table2,
{
"Encrypted Text",
each
let
a = List.Accumulate({" ", ",", "@", "."}, _, (s, c) => Text.Replace(s, c, " " & c & " ")),
b = Text.Combine(
List.Transform(
Text.Split(a, " "),
each
let
b1 = List.Transform(List.Split(Text.ToList(_), 2), Text.Combine),
b2 = Text.Combine(
List.Transform(
List.ReplaceMatchingItems(b1, Zip),
each try Text.Lower(Text.From(_)) otherwise _
)
)
in
b2
),
" "
)
in
List.Accumulate(
{{" ", " "}, {" , ", ","}, {" @ ", "@"}, {" . ", "."}},
b,
(s, c) => Text.Replace(s, c{0}, c{1})
)
}
)
in
SolPower Query solution 6 for Decrypt Polybius Cipher Grid, proposed by Venkata Rajesh:
let
Source = Data,
Ouput = Table.AddColumn(
Source,
"Expected",
each [
matrix = Table.ToRows(Matrix),
x = Text.ToList([Encrypted Text]),
y = List.Alternate(
List.Select({0 .. List.Count(x) - 1}, each List.Contains({"0" .. "9"}, x{_})),
1,
1,
1
),
z = List.Accumulate(
y,
[Encrypted Text],
(state, current) =>
Text.ReplaceRange(
state,
current - List.Count(List.Select(y, each _ < current)),
2,
Text.Lower(matrix{Number.From(x{current}) - 1}{Number.From(x{current + 1})})
)
)
][z]
)
in
OuputSolving the challenge of Decrypt Polybius Cipher Grid with Excel
Excel solution 1 for Decrypt Polybius Cipher Grid, proposed by Bo Rydobon 🇹🇭:
=MAP(
I2:I7,
LAMBDA(
a,
LET(
d,
TEXTSPLIT(
a,
SEQUENCE(
6
),
,
1
),
LOWER(
TEXTJOIN(
d,
,
MAP(
TEXTSPLIT(
a,
d
),
LAMBDA(
b,
CONCAT(
XLOOKUP(
MID(
b,
SEQUENCE(
9,
,
,
2
),
2
),
TOCOL(
A3:A8&B2:G2
),
TOCOL(
B3:G8
),
""
)
)
)
)
)
)
)
)
)Excel solution 2 for Decrypt Polybius Cipher Grid, proposed by Bo Rydobon 🇹🇭:
=LET(
R,
LAMBDA(
R,
a,
b,
LET(
l,
LEFT(
a
),
c,
INDEX(
LOWER(
B3:G8
),
l,
MID(
a,
2,
1
)
),
IF(
a="",
b,
R(
R,
MID(
a,
3-ISERR(
c
),
99
),
b&IFERROR(
c,
l
)
)
)
)
),
MAP(
I2:I7,
LAMBDA(
a,
R(
R,
a,
)
)
)
)Excel solution 3 for Decrypt Polybius Cipher Grid, proposed by Bo Rydobon 🇹🇭:
=REDUCE(I2:I7,SEQUENCE(99),LAMBDA(a,v,IFERROR(REPLACE(a,v,2,LOWER(INDEX(B3:G8,MID(a,v,1),MID(a,v+1,1)))),a)))Excel solution 4 for Decrypt Polybius Cipher Grid, proposed by Rick Rothstein:
=MAP(I2:I7,
LAMBDA(z,
CONCAT(LET(a,
"abcdefghijklmnopqrstuvwxyz0123456789",
t,
REDUCE(
"",
MID(
z,
SEQUENCE(
LEN(
z
)
),
1
),
LAMBDA(
n,
x,
n&IF(
ISNUMBER(
FIND(
x,
a
)
),
x,
x&x
)
)
),
MAP(MID(
t,
SEQUENCE(
LEN(
t
),
,
,
2
),
2
),
LAMBDA(x,
LET(n,
96+6*(LEFT(
x
)-1)+RIGHT(
x
),
IF(
ISNUMBER(
0+x
),
CHAR(
IF(
n>122,
n-75,
n
)
),
LEFT(
x
)
))))))))Excel solution 5 for Decrypt Polybius Cipher Grid, proposed by John V.:
=REDUCE(
I2:I7,
ROW(
1:30
),
LAMBDA(
a,
v,
IF(
1-ISERR(
-MID(
a,
v,
1
)
),
REPLACE(
a,
v,
2,
INDEX(
LOWER(
B3:G8
),
MID(
a,
v,
1
),
MID(
a,
1+v,
1
)
)
),
a
)
)
)Excel solution 6 for Decrypt Polybius Cipher Grid, proposed by محمد حلمي:
=MAP(I2:I7,LAMBDA(a,LET(s,ROW(1:99),d,MID(a,s,1),e,MID(CONCAT(IFERROR(--d,d&d)),s*2-1,2),LOWER(CONCAT(
IFNA(XLOOKUP(e,TOCOL(A3:A8&B2:G2),TOCOL(B3:G8)),LEFT(e)))))))Excel solution 7 for Decrypt Polybius Cipher Grid, proposed by محمد حلمي:
=MAP(I2:I7,LAMBDA(A,LOWER(CONCAT(TOCOL(XLOOKUP(MID(TEXTSPLIT(A,{" ","@",",","."}),
SEQUENCE(20,,,2),2),TOCOL(A3:A8&B2:G2),TOCOL(B3:G8),""),,1)))))Excel solution 8 for Decrypt Polybius Cipher Grid, proposed by Kris Jaganah:
=MAP(
I2:I7,
LAMBDA(
v,
LET(
a,
B3:G8,
b,
TOCOL(
a
),
c,
MAP(
b,
LAMBDA(
x,
TOCOL(
IFS(
x=a,
A3:A$8
)&IFS(
x=a,
B2:G2
),
3
)
)
),
d,
TEXTSPLIT(
v,
,
{" ",
"@",
".",
","}
),
f,
IFNA(
DROP(
REDUCE(
"",
d,
LAMBDA(
u,
v,
VSTACK(
u,
IFERROR(
MID(
v,
SEQUENCE(
,
LEN(
v
)/2,
,
2
),
2
),
0
)
)
)
),
1
),
""
),
LOWER(
TRIM(
CONCAT(
HSTACK(
XLOOKUP(
f,
c,
b,
""
),
MID(
v,
SCAN(
0,
d,
LAMBDA(
r,
s,
LEN(
s
)+r+1
)
),
1
)
)
)
)
)
)
)
)Excel solution 9 for Decrypt Polybius Cipher Grid, proposed by Julian Poeltl:
=MAP(I2:I7,LAMBDA(E,LET(SP,MID(E,SEQUENCE(LEN(E)),1),N,ISNUMBER(--SP),M,MOD(SCAN(0,--N,LAMBDA(A,B,A+B)),2),T,TOROW(MAP(SP,SEQUENCE(ROWS(SP)),N,M,LAMBDA(S,A,N,M,IF(N,IF(M,MID(E,A,2),X),S))),3),LOWER(CONCAT(IFNA(XLOOKUP(T,TOCOL(A3:A8&B2:G2),TOCOL(B3:G8)),T))))))Excel solution 10 for Decrypt Polybius Cipher Grid, proposed by Timothée BLIOT:
=MAP(
I2:I7,
LAMBDA(
v,
LET(
A,
TEXTSPLIT(
v,
SEQUENCE(
10,
,
0
),
,
1
),
B,
TEXTSPLIT(
v,
UNIQUE(
TOCOL(
HSTACK(
A,
" "
)
)
)
),
C,
LOWER(
MAP(
B,
LAMBDA(
& x,
CONCAT(
LET(
Z,
WRAPROWS(
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
2
),
INDEX(
$B$3:$G$8,
INDEX(
Z,
,
1
),
INDEX(
Z,
,
2
)
)
)
)
)
)
),
CONCAT(
TOCOL(
VSTACK(
C,
A
),
3,
1
)
)
)
)
)Excel solution 11 for Decrypt Polybius Cipher Grid, proposed by Hussein SATOUR:
=MAP(
I2:I7,
LAMBDA(
z,
LET(
b,
REDUCE(
"",
MID(
z,
SEQUENCE(
LEN(
z
)
),
1
),
LAMBDA(
x,
y,
x & IF(
ISERR(
--y
),
y&y,
y
)
)
),
a,
MID(
b,
SEQUENCE(
LEN(
b
)/2,
,
,
2
),
2
),
CONCAT(
IFERROR(
INDEX(
B3:G8,
LEFT(
a
),
RIGHT(
a
)
),
LEFT(
a
)
)
)
)
)
)Excel solution 12 for Decrypt Polybius Cipher Grid, proposed by Oscar Mendez Roca Farell:
=MAP(I2:I7,
LAMBDA(b,
LET(_s,
SEQUENCE(
36,
,
97
),
_m,
HSTACK(TOCOL(
A3:A8&B2:G2
),
CHAR(_s-75*(_s>122))),
_d,
TEXTSPLIT(
b,
,
SEQUENCE(
10
)-1,
1
),
TEXTJOIN(
_d,
,
MAP(
TEXTSPLIT(
b,
,
_d
),
LAMBDA(
a,
CONCAT(
VLOOKUP(
MID(
a,
2*SEQUENCE(
LEN(
a
)/2
)-1,
2
),
_m,
2,
)
)
)
)
))))Excel solution 13 for Decrypt Polybius Cipher Grid, proposed by Sunny Baggu:
=MAP(
I2:I7,
LAMBDA(
x,
LET(
_a,
TEXTSPLIT(
x,
,
{" ",
", ",
"@",
"."},
1
),
_b,
TEXTSPLIT(
x,
,
_a,
1
),
_c,
DROP(
REDUCE(
"",
_a,
LAMBDA(
a,
v,
VSTACK(
a,
LOWER(
CONCAT(
MAP(
MID(
v,
SEQUENCE(
LEN(
v
) / 2,
,
1,
2
),
2
),
LAMBDA(
a,
INDEX(
B3:G8,
XMATCH(
LEFT(
a
) + 0,
A3:A8
),
XMATCH(
RIGHT(
a
) + 0,
B2:G2
)
)
)
)
)
)
)
)
),
1
),
TRIM(
CONCAT(
IFNA(
_c & _b,
_c
)
)
)
)
)
)Excel solution 14 for Decrypt Polybius Cipher Grid, proposed by LEONARD OCHEA 🇷🇴:
=LET(d,I2:I7,t,B3:G8,MAP(d,LAMBDA(x,LET(s,MID(x,SEQUENCE(LEN(x)),1),d,DROP(REDUCE(0,s,LAMBDA(a,b,VSTACK(a,IF(ISNUMBER(b*1),b,VSTACK(b,b))))),1),m,WRAPROWS(d,2),f,INDEX(m,,1),c,INDEX(m,,2),LOWER(CONCAT(IFERROR(INDEX(t,f,c),f)))))))Excel solution 15 for Decrypt Polybius Cipher Grid, proposed by Abdallah Ally:
=MAP(
I2:I7,
LAMBDA(
e,
LOWER(
LET(
a,
REDUCE(
"",
MID(
e,
SEQUENCE(
LEN(
e
)
),
1
),
LAMBDA(
x,
y,
IF(
ISNUMBER(
VALUE(
y
)
),
x&y,
x&REPT(
y,
2
)
)
)
),
b,
MID(
a,
SEQUENCE(
LEN(
a
)/2,
,
1,
2
),
2
),
c,
HSTACK(
TOCOL(
TOCOL(
SEQUENCE(
6
)
)&TOROW(
SEQUENCE(
6
)
)
),
VSTACK(
CHAR(
SEQUENCE(
26,
,
65
)
),
CHAR(
SEQUENCE(
10,
,
48
)
)
)
),
REDUCE(
"",
b,
LAMBDA(
r,
s,
IFERROR(
r&VLOOKUP(
TEXT(
s,
"00"
),
c,
2,
FALSE
),
r&LEFT(
s,
1
)
)
)
)
)
)
)
)Excel solution 16 for Decrypt Polybius Cipher Grid, proposed by Charles Roldan:
=LET(M,
LAMBDA(
g,
g(
g
)
),
f,
LAMBDA(Key,
M(LAMBDA(g,
LAMBDA(Code,
LET(a,
LEFT(
Code
),
b,
RIGHT(
LEFT(
Code,
2
)
),
n,
ISNUMBER(
--a
),
IF(LEN(
a
),
IF(
n,
INDEX(
Key,
a,
b
),
a
) & g(
g
)(REPLACE(
Code,
1,
1 + n,
)),
)))))),
MAP(
I2:I7,
f(
LOWER(
B3:G8
)
)
))Excel solution 17 for Decrypt Polybius Cipher Grid, proposed by JvdV -:
=REDUCE(I2:I7,ROW(1:99),LAMBDA(x,y,LET(z,MID(x,y,2),IFERROR(REPLACE(x,y,2,INDEX(LOWER(B3:G8),LEFT(z),RIGHT(z))),x))))Excel solution 18 for Decrypt Polybius Cipher Grid, proposed by Giorgi Goderdzishvili:
=LET(
txt,
J5,
grid,
$C$5:$H$10,
crs,
MID(
txt,
SEQUENCE(
,
LEN(
txt
)
),
1
),
rcrs,
IFERROR(
--crs,
crs
),
list,
TOCOL(
SEQUENCE(
6
)&SEQUENCE(
,
6
)
),
gr,
TOCOL(
grid
),
rtrn,
MAP(
rcrs,
LAMBDA(
x,
XLOOKUP(
x,
gr,
list,
x
)
)
),
CONCAT(
rtrn
)
)Excel solution 19 for Decrypt Polybius Cipher Grid, proposed by Quadri Olayinka Atharu:
=MAP(I2:I7,
LAMBDA(x,LET(y,SUBSTITUTE(x," "," "),z,IF(COUNT(FIND({",","@"},x))>0,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(y,",",", "),"@"," @"),".",". "),y),
s,MID(z,SEQUENCE(LEN(z),,,2),2),
SUBSTITUTE(LOWER(TRIM(CONCAT(MAP(s,LAMBDA(x,(IFERROR(TOCOL(IF(A3:A8=--LEFT(x)*(--RIGHT(x)=B2:G2),B3:G8,k),2),x)))))))," @","@"))))Excel solution 20 for Decrypt Polybius Cipher Grid, proposed by Ricardo Alexis Domínguez Hernández:
=BYROW(
I2:I7,
LAMBDA(
x,
LOWER(
CONCAT(
IFERROR(
INDEX(
$B$3:$G$8,
CHOOSECOLS(
WRAPROWS(
MID(
CONCAT(
BYCOL(
MID(
x,
SEQUENCE(
,
LEN(
x
)
),
1
),
LAMBDA(
x,
IF(
AND(
CODE(
x
)>=49,
CODE(
x
)<=54
),
x*1,
x&x
)
)
)
),
SEQUENCE(
,
LEN(
CONCAT(
BYCOL(
MID(
x,
SEQUENCE(
,
LEN(
x
)
),
1
),
LAMBDA(
x,
IF(
AND(
CODE(
x
)>=49,
CODE(
x
)<=54
& ),
x*1,
x&x
)
)
)
)
)
),
1
),
2
),
1
),
CHOOSECOLS(
WRAPROWS(
MID(
CONCAT(
BYCOL(
MID(
x,
SEQUENCE(
,
LEN(
x
)
),
1
),
LAMBDA(
x,
IF(
AND(
CODE(
x
)>=49,
CODE(
x
)<=54
),
x*1,
x&x
)
)
)
),
SEQUENCE(
,
LEN(
CONCAT(
BYCOL(
MID(
x,
SEQUENCE(
,
LEN(
x
)
),
1
),
LAMBDA(
x,
IF(
AND(
CODE(
x
)>=49,
CODE(
x
)<=54
),
x*1,
x&x
)
)
)
)
)
),
1
),
2
),
2
)
),
CHOOSECOLS(
WRAPROWS(
MID(
CONCAT(
BYCOL(
MID(
x,
SEQUENCE(
,
LEN(
x
)
),
1
),
LAMBDA(
x,
IF(
AND(
CODE(
x
)>=49,
CODE(
x
)<=54
),
x*1,
x&x
)
)
)
),
SEQUENCE(
,
LEN(
CONCAT(
BYCOL(
MID(
x,
SEQUENCE(
,
LEN(
x
)
),
1
),
LAMBDA(
x,
IF(
AND(
CODE(
x
)>=49,
CODE(
x
)<=54
),
x*1,
x&x
)
)
)
)
)
),
1
),
2
),
1
)
)
)
)
)
)Solving the challenge of Decrypt Polybius Cipher Grid with Excel VBA
Excel VBA solution 1 for Decrypt Polybius Cipher Grid, proposed by Nicolas Micot:
Function f_polybiusCipher(ByVal tableau As Variant, ByVal encrypted As String) As String
Dim pos As Integer
Dim textPart As String, resultat As String
pos = 1
While pos <= Len(encrypted) - 1
textPart = Mid(encrypted, pos, 2)
If textPart Like "##" Then
textPart = tableau(Val(Left(textPart, 1)), Val(Right(textPart, 1)))
pos = pos + 2
Else
textPart = Left(textPart, 1)
pos = pos + 1
End If
resultat = resultat & textPart
Wend
f_polybiusCipher = LCase(resultat)
End Function
&