Bifid Cipher Suppose we want to encrypt the message “BATTLE”. We prepare 5×5 grid as shown where J is replaced with I. Now, message is written at top and row and column positions are written in next 2 rows BATTLE 114431 214415 Now, concat row 2 and 3. 114431214415 Extract first 2 letters, then next 2 letters and so on 11 44 31 21 44 15 Now, if it is ij, it means lookup intersection of row i and column j. Hence, we retrieve following alphabets which the encrypted text. ATLFTE
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 432
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Encrypt with Bifid Cipher Method with Power Query
Power Query solution 1 for Encrypt with Bifid Cipher Method, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Ans = Table.AddColumn(
Source,
"Ans",
each Text.Combine(
List.Transform(
List.Split(
List.Combine(
List.Zip(
List.Transform(
Text.ToList([Plain Text]),
(c) =>
let
n = Character.ToNumber(c) - 97,
m = n - Number.From(n > 8)
in
{Number.IntegerDivide(m, 5), Number.Mod(m, 5)}
)
)
),
2
),
(o) =>
let
p = o{0} * 5 + o{1}
in
Character.FromNumber(p + (Number.From(p > 8) + 97))
)
)
)
in
AnsPower Query solution 2 for Encrypt with Bifid Cipher Method, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[ Name = "data" ]}[Content],
List = { "a" .. "i", "k" .. "z" },
Return = Table.AddColumn (
Source,
"Answer",
each [
S = Text.ToList ( Text.Replace ( [Plain Text], "j", "i" ) ),
P = List.ReplaceMatchingItems ( S, List.Zip ( { List, { 0 .. 25 } } ) ),
I = List.Transform ( P, ( f ) => Number.IntegerDivide ( f, 5 ) ),
J = List.Transform ( P, ( f ) => Number.Mod ( f, 5 ) ),
C = List.Split ( I & J, 2 ),
O = List.Transform ( C, ( f ) => List{f{0} * 5 + f{1}} ),
R = Text.Combine ( O )
][R]
),
in
Return
Power Query solution 3 for Encrypt with Bifid Cipher Method, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Matrix = Table.UnpivotOtherColumns(
Table.AddIndexColumn(
Table.FromRows(List.Split({"a" .. "i", "k" .. "z"}, 5), {"1" .. "5"}),
"i",
1
),
{"i"},
"j",
"A"
),
Return = Table.AddColumn(
Source,
"Answer",
each [
S = Text.ToList(Text.Replace([Plain Text], "j", "i")),
I = List.Transform(S, (f) => Text.From(Matrix{[A = f]}[i])),
J = List.Transform(S, (f) => Matrix{[A = f]}[j]),
C = List.Split(I & J, 2),
O = List.Transform(C, (f) => Matrix{[i = Number.From(f{0}), j = f{1}]}[A]),
R = Text.Combine(O)
][R]
)
in
ReturnPower Query solution 4 for Encrypt with Bifid Cipher Method, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.AddColumn(
Source,
"Answer",
each
let
a = List.Split(List.RemoveItems({"a" .. "z"}, {"j"}), 5),
b = Table.ToColumns(Table.FromRows(a)),
c = List.Transform({a, b}, (x) => List.Transform({1 .. 5}, each {_} & x{_ - 1})),
d = List.Transform(
List.Split(
List.Combine(
List.Transform(
c,
(y) =>
List.Transform(
Text.ToList([Plain Text]),
each List.RemoveNulls(
List.Transform(
y,
(x) => if List.Contains(x, _) then Text.From(List.First(x)) else null
)
){0}
)
)
),
2
),
each Number.From(Text.Combine(_))
),
e = Table.FromRows(c{0}, {"A", "1" .. "5"}),
f = Table.TransformColumns(
Table.UnpivotOtherColumns(e, {"A"}, "At", "Va"),
{"A", Text.From}
),
g = Table.ToRows(Table.AddColumn(f, "N", each Number.From([A] & [At]))[[N], [Va]]),
i = Text.Combine(List.ReplaceMatchingItems(d, g))
in
i
)
in
SolPower Query solution 5 for Encrypt with Bifid Cipher Method, proposed by Luan Rodrigues:
let
lt = List.Transform,
Fonte = Table.ToRows(
Table.Combine(
lt(
List.Zip(
{
List.Split({"a" .. "i", "k" .. "z"}, 5),
lt({"1" .. "5"}, each List.Repeat({_}, 5)),
List.Repeat({{"1" .. "5"}}, 5)
}
),
Table.FromColumns
)
)
),
res = Table.AddColumn(
Tabela1,
"Personalizar",
each [
a = Text.ToList([Plain Text]),
b = List.ReplaceMatchingItems(a, lt(Fonte, each List.FirstN(_, 2))),
c = Text.Combine(
lt(
{b, List.ReplaceMatchingItems(a, lt(Fonte, each {List.First(_), List.Last(_)}))},
Text.Combine
)
),
d = lt(List.Split(Text.ToList(c), 2), Text.Combine),
e = Text.Combine(List.ReplaceMatchingItems(d, lt(Fonte, each {_{1} & _{2}} & {_{0}})))
][e]
)
in
resPower Query solution 6 for Encrypt with Bifid Cipher Method, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.AddColumn(
S,
"Answer Expected",
each
let
T = Table.AddColumn,
P = Table.FromColumns({{"A" .. "I", "K" .. "Z"}, {0 .. 24}}),
R = T(P, "R", each Number.IntegerDivide([Column2], 5) + 1),
C = T(R, "C", each Number.Mod([Column2], 5) + 1),
M = T(C, "M", each Text.From([R]) & Text.From([C])),
a = Table.FromColumns({Text.ToList(Text.Upper([Plain Text]))}),
b = Table.ReplaceValue(a, "J", "I", Replacer.ReplaceText, {"Column1"}),
c = T(b, "R", each M[R]{List.PositionOf(M[Column1], [Column1])}),
d = T(c, "C", each M[C]{List.PositionOf(M[Column1], [Column1])}),
e = Table.FromColumns(
{
List.Transform(
List.Split(d[R] & d[C], 2),
each Text.Combine(List.Transform(_, Text.From))
)
}
),
f = T(e, "T", each M[Column1]{List.PositionOf(M[M], [Column1])})[T],
g = Text.Lower(Text.Combine(f))
in
g
)
in
SolSolving the challenge of Encrypt with Bifid Cipher Method with Excel
Excel solution 1 for Encrypt with Bifid Cipher Method, proposed by Bo Rydobon 🇹🇭:
=MAP(A2:A10,
LAMBDA(a,
LET(n,
CODE(
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
)
)-97,
m,
n-(n>8),
o,
MMULT(
WRAPROWS(
VSTACK(
INT(
m/5
),
MOD(
m,
5
)
),
2
),
{5;1}
),
CONCAT(CHAR(o+(o>8)+97)))))Excel solution 2 for Encrypt with Bifid Cipher Method, proposed by John V.:
=MAP(A2:A10,
LAMBDA(x,
LET(s,
SEQUENCE(
LEN(
x
)
),
c,
CODE(
MID(
x,
s,
1
)
)-97,
a,
11+BASE(c-(c>9),
5),
b,
DECIMAL(
MID(
CONCAT(
LEFT(
a
),
RIGHT(
a
)
),
2*s-1,
2
)-11,
5
),
CONCAT(CHAR(97+b+(b>8))))))Excel solution 3 for Encrypt with Bifid Cipher Method, proposed by محمد حلمي:
=MAP(A2:A10,
LAMBDA(a,
LET(
g,
WRAPROWS(
CHAR(
VSTACK(
SEQUENCE(
9
),
SEQUENCE(
16
)+10
)+96
),
5
),
e,
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
i,
WRAPROWS(VSTACK(MAP(e,
LAMBDA(x,
SUM((g=x)*SEQUENCE(
5
)))),
MAP(e,
LAMBDA(x,
SUM((g=x)*SEQUENCE(
,
5
))))),
2),
CONCAT(
INDEX(
g,
TAKE(
i,
,
1
),
DROP(
i,
,
1
)
)
))))Excel solution 4 for Encrypt with Bifid Cipher Method, proposed by Kris Jaganah:
=MAP(A2:A10,
LAMBDA(x,
LET(a,
SEQUENCE(
25
),
b,
INT(
a/5+0.99
),
c,
TOCOL(
WRAPCOLS(
b,
5
)
),
d,
CHAR(
IF(
a>9,
a+1,
a
)+96
),
e,
MID(
x,
SEQUENCE(
,
LEN(
x
)
),
1
),
CONCAT(XLOOKUP(BYROW(WRAPROWS(HSTACK(BYCOL((e=d)*b,
SUM),
BYCOL((e=d)*c,
SUM)),
2),
CONCAT),
b&c,
d)))))Excel solution 5 for Encrypt with Bifid Cipher Method, proposed by Julian Poeltl:
=MAP(
A2:A10,
LAMBDA(
T,
LET(
LT,
LEN(
T
),
SP,
MID(
T,
SEQUENCE(
,
LT
),
1
),
SQ,
SEQUENCE(
25
),
L,
CHAR(
96+IF(
SQ>9,
SQ+1,
SQ
)
),
R,
ROUNDUP(
SQ/5,
0
),
C,
MOD(
SQ-1,
5
)+1,
CC,
CONCAT(
XLOOKUP(
SP,
L,
R
),
XLOOKUP(
SP,
L,
C
)
),
EX,
MID(
CC,
SEQUENCE(
,
LT,
,
2
),
2
),
CONCAT(
XLOOKUP(
LEFT(
EX,
1
)&RIGHT(
EX,
1
),
R&C,
L
)
)
)
)
)Excel solution 6 for Encrypt with Bifid Cipher Method, proposed by Timothée BLIOT:
=MAP(
A2:A10,
LAMBDA(
z,
LET(
A,
SEQUENCE(
5,
5
),
B,
CHAR(
IF(
A>9,
A+1,
A
)+96
),
C,
MID(
z,
SEQUENCE(
LEN(
z
)
),
1
),
D,
LAMBDA(
n,
MAP(
C,
LAMBDA(
x,
TOCOL(
IF(
x=B,
n,
1/0
),
3
)
)
)
),
G,
WRAPROWS(
VSTACK(
D(
ROW(
1:5
)
),
D(
COLUMN(
1:5
)
)
),
2
),
CONCAT(
MAP(
TAKE(
G,
,
1
),
TAKE(
G,
,
-1
),
LAMBDA(
x,
y,
INDEX(
B,
x,
y
)
)
)
)
)
)
)Excel solution 7 for Encrypt with Bifid Cipher Method, proposed by Hussein SATOUR:
=MAP(A2:A10,
LAMBDA(x,
LET(a,
CHAR(
VSTACK(
SEQUENCE(
9
),
SEQUENCE(
16,
,
11
)
)+96
),
b,
MID(
SUBSTITUTE(
x,
"j",
"I"
),
SEQUENCE(
LEN(
x
)
),
1
),
c,
ROUNDUP(
XMATCH(
b,
a
)/5,
0
),
d,
MOD(
XMATCH(
b,
a
),
5
),
e,
IF(
d=0,
5,
d
),
f,
CONCAT(
c,
e
),
g,
MID(
f,
SEQUENCE(
LEN(
f
)/2,
,
,
2
),
2
),
CONCAT(INDEX(a,
(LEFT(
g
)-1)*5+RIGHT(
g
))))))Excel solution 8 for Encrypt with Bifid Cipher Method, proposed by Oscar Mendez Roca Farell:
=MAP(A2:A10,
LAMBDA(a,
LET(r,
ROW(
1:5
),
n,
TOCOL(
r&"|"&TOROW(
r
)
),
m,
CODE(
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
)
)-96,
w,
WRAPROWS(TOCOL(TEXTSPLIT(CONCAT(INDEX(n,
m-(m>10))&"-"),
"|",
"-",
1),
,
1),
2),
x,
96+XMATCH(
BYROW(
w,
LAMBDA(
i,
TEXTJOIN(
"|",
,
i
)
)
),
n
),
CONCAT(CHAR(x+(x>105))))))Excel solution 9 for Encrypt with Bifid Cipher Method, proposed by Sunny Baggu:
=MAP(
A2:A10,
LAMBDA(
t,
LET(
_a,
SEQUENCE(
26,
,
97
),
_s,
SEQUENCE(
5
),
_b,
TOCOL(
CHAR(
WRAPROWS(
TOCOL(
IF(
_a <> CODE(
"j"
& ),
_a,
x
),
3
),
5
)
)
),
_c,
TOCOL(
_s & TOROW(
_s
)
),
_m,
MID(
t,
SEQUENCE(
LEN(
t
)
),
1
),
_n,
CONCAT(
XLOOKUP(
_m,
_b,
_c
)
),
_ns,
WRAPCOLS(
TOCOL(
MID(
_n,
SEQUENCE(
LEN(
_n
) / 2,
2
),
1
),
3,
1
),
2
),
_num,
TAKE(
_ns,
1
) & TAKE(
_ns,
-1
),
CONCAT(
XLOOKUP(
_num,
_c,
_b
)
)
)
)
)Excel solution 10 for Encrypt with Bifid Cipher Method, proposed by LEONARD OCHEA 🇷🇴:
=MAP(A2:A10,
LAMBDA(a,
LET(s,
SEQUENCE(
LEN(
a
)
),
i,
CODE(
MID(
a,
s,
1
)
),
k,
IF(
i>105,
i-1,
i
),
f,
INT((k+3)/5)-19,
c,
MOD(
k+3,
5
)+1,
j,
MID(
CONCAT(
f,
c
),
2*s-1,
2
),
x,
91+MID(
j,
1,
1
)*5+MID(
j,
2,
1
),
CONCAT(
CHAR(
IF(
x>105,
x+1,
x
)
)
))))Excel solution 11 for Encrypt with Bifid Cipher Method, proposed by Abdallah Ally:
=MAP(
A2:A10,
LAMBDA(
x,
LET(
a,
SUBSTITUTE(
x,
"j",
"I"
),
b,
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
c,
CHAR(
VSTACK(
SEQUENCE(
9
),
SEQUENCE(
16,
,
11
)
)+96
),
d,
SEQUENCE(
5
)&SEQUENCE(
,
5
),
e,
TOCOL(
d
),
f,
TOCOL(
d,
,
1
),
g,
CONCAT(
INDEX(
LEFT(
e
),
MATCH(
b,
c,
0
)
),
INDEX(
LEFT(
f
),
MATCH(
b,
c,
0
)
)
),
h,
MID(
g,
SEQUENCE(
LEN(
g
)/2,
,
,
2
),
2
),
CONCAT(
INDEX(
c,
MATCH(
h,
e,
0
)
)
)
)
)
)Excel solution 12 for Encrypt with Bifid Cipher Method, proposed by Charles Roldan:
=LET(Grid,
{"a",
"b",
"c",
"d",
"e";
"f",
"g",
"h",
"i",
"k";
"l",
"m",
"n",
"o",
"p";
"q",
"r",
"s",
"t",
"u";
"v",
"w",
"x",
"y",
"z"},
_Each,
LAMBDA(
f,
LAMBDA(
x,
BYROW(
x,
f
)
)
),
CONCAT,
LAMBDA(
x,
CONCAT(
x
)
),
_Encypher,
_Each(
LAMBDA(
Row,
INDEX(
Grid,
INDEX(
Row,
1
),
INDEX(
Row,
2
)
)
)
),
_Each(LAMBDA(Text,
LET(_Loc,
LAMBDA(Mat,
_Each(
LAMBDA(
Char,
XMATCH(
TRUE,
ISNUMBER(
SEARCH(
Char,
BYROW(
Mat,
CONCAT
)
)
)
)
)
)(MID(
Text,
SEQUENCE(
LEN(
Text
)
),
1
))),
CONCAT(
_Encypher(
WRAPROWS(
VSTACK(
_Loc(
Grid
),
_Loc(
TRANSPOSE(
Grid
)
)
),
2
)
)
))))
)(A2:A10)Excel solution 13 for Encrypt with Bifid Cipher Method, proposed by Sandeep Marwal:
=LET(
alphabaterange,
$B$2:$F$6,
inputword,
H1,
word,
MID(
inputword,
SEQUENCE(
,
LEN(
inputword
)
),
1
),
rw,
MAP(
word,
LAMBDA(
letter,
SUM(
BYCOL(
alphabaterange,
LAMBDA(
a,
IFERROR(
XMATCH(
letter,
a
),
0
)
)
)
)
)
),
cl,
MAP(
word,
LAMBDA(
letter,
SUM(
BYROW(
alphabaterange,
LAMBDA(
a,
IFERROR(
XMATCH(
letter,
a
),
0
)
)
)
)
)
),
rwcl,
TEXTJOIN(
"",
,
TEXTJOIN(
"",
,
rw
),
TEXTJOIN(
"",
,
cl
)
),
newseq,
MID(
rwcl,
SEQUENCE(
,
LEN(
rwcl
)/2,
1,
2
),
2
),
Outputword,
CONCAT(
INDEX(
alphabaterange,
--LEFT(
newseq
),
--RIGHT(
newseq
)
)
),
Outputword
)Excel solution 14 for Encrypt with Bifid Cipher Method, proposed by Songglod P.:
=LET(
alphabet,
LET(
letters,
UNICHAR(
SEQUENCE(
1,
26,
UNICODE(
"a"
)
)
),
FILTER(
letters,
letters <> "j"
)
),
nums,
TOROW(
SEQUENCE(
5
) & SEQUENCE(
1,
5
)
),
encode,
LAMBDA(
a,
XLOOKUP(
a,
alphabet,
nums
)
),
cipher,
LAMBDA(
n,
XLOOKUP(
n,
nums,
alphabet
)
),
MAP(
A2:A10,
LAMBDA(
text,
LET(
t,
SUBSTITUTE(
text,
"j",
"i"
),
chars,
MID(
t,
SEQUENCE(
1,
LEN(
t
)
),
1
),
encoded,
CONCAT(
LEFT(
encode(
chars
),
1
),
RIGHT(
encode(
chars
),
1
)
),
code,
MID(
encoded,
SEQUENCE(
1,
LEN(
encoded
) / 2,
,
2
),
2
),
CONCAT(
cipher(
code
)
)
)
)
)
)Solving the challenge of Encrypt with Bifid Cipher Method with Python
Python solution 1 for Encrypt with Bifid Cipher Method, proposed by Konrad Gryczan, PhD:
import pandas as pd
import re
input = pd.read_excel("432 Bifid Cipher_Part 1.xlsx", usecols="A", nrows=10)
test = pd.read_excel("432 Bifid Cipher_Part 1.xlsx", usecols="B", nrows=10)
def create_coding_square():
alphabet = 'abcdefghiklmnopqrstuvwxyz'
return {letter: ((index // 5) + 1, (index % 5) + 1) for index, letter in enumerate(alphabet)}
def bifid_encode(text):
coding_square = create_coding_square()
text = text.replace('j', 'i')
text = ''.join(filter(str.isalpha, text))
coords = [coding_square[letter] for letter in text if letter in coding_square]
coords = list(zip(*coords))
coords = coords[0] + coords[1]
coords = [coords[i:i + 2] for i in range(0, len(coords), 2)]
text = ''.join([list(coding_square.keys())[list(coding_square.values()).index((x[0], x[1]))] for x in coords])
return text
input['Answer Expected'] = input['Plain Text'].apply(bifid_encode)
print(input['Answer Expected'].equals(test['Answer Expected'])) # True
Solving the challenge of Encrypt with Bifid Cipher Method with Python in Excel
Python in Excel solution 1 for Encrypt with Bifid Cipher Method, proposed by Abdallah Ally:
import pandas as pd
from string import ascii_lowercase
from math import ceil
file_path = 'Excel_Challenge_432 - Bifid Cipher_Part 1.xlsx'
df = pd.read_excel(file_path)
# Perform data transformation and cleansing
def bifid_cipher(col):
values = []
for index, char in enumerate(ascii_lowercase.replace('j', ''), start=1):
i = ceil(index / 5)
j = index - (index // 5) * 5
values.append((char, str(i), str(5 if j == 0 else j)))
s1 = [x[1] for char in col.replace('j', 'i') for x in values if char == x[0]]
s2 = [x[2] for char in col.replace('j', 'i') for x in values if char == x[0]]
string = ''.join(s1 + s2)
encrypted_string = ''
for i in range(0, len(string), 2):
sample = string[i: i + 2]
for value in values:
if sample[0] == value[1] and sample[1] == value[2]:
encrypted_string += value[0]
return encrypted_string
df['My String'] = df['Plain Text'].apply(bifid_cipher)
print(f'nExpected Results:n{df.iloc[:, [0, 1]]}nnMy Results:n{df.iloc[:, [0, 2]]}')
&&