(Baconian Cipher – This problem is case sensitive) A/a is 0, B/b is 1, C/c is 2…..Z/z is 25. Binary representations of these numbers in 5 digits is 00000, 00001, 00010….11001. Replace 0 with a and 1 with b in these binary numbers. Hence, these will become aaaaa, aaaab, aaaba…..bbaab. In case of upper case, prefix these with b and in case of lower case, prefix these with a. Hence, A will become baaaaa and a will be aaaaaa. Now, to encrypt any word, you can replace the letters in the string with these text representations.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 212
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Case-Sensitive Baconian Cipher with Power Query
Power Query solution 1 for Case-Sensitive Baconian Cipher, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Ans = Table.TransformRows(
Source,
each Text.Combine(
List.Transform(
Text.ToList([Words]),
each
let
c = Character.ToNumber(_) - 1
in
(if c < 96 then "b" else "a")
& Text.End(
List.Accumulate(
{0 .. 5},
{c, ""},
(s, l) =>
let
n = Number.Power(2, 5 - l)
in
{
Number.Mod(s{0}, n),
s{1} & Character.FromNumber(Number.IntegerDivide(s{0}, n) + 97)
}
){1},
5
)
)
)
)
in
AnsPower Query solution 2 for Case-Sensitive Baconian Cipher, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
ToBinary = (n) =>
Text.PadStart(
Text.Combine(
List.Transform(
List.Reverse(
List.Generate(
() => [Q = Number.IntegerDivide(n, 2), R = Number.Mod(n, 2)],
each [Q] <> 0 or [R] <> 0,
each [Q = Number.IntegerDivide([Q], 2), R = Number.Mod([Q], 2)],
each [R]
)
),
each if _ = 0 then "a" else "b"
),
""
),
5,
"a"
),
Solution = Table.TransformRows(
Source,
each Text.Combine(
List.Transform(
Text.ToList([Words]),
each
let
a = Character.ToNumber(_),
m = if a >= 97 then 97 else 65
in
Character.FromNumber(97 + Number.From(m = 65)) & ToBinary(Number.Mod(a, m))
),
""
)
)
in
SolutionPower Query solution 3 for Case-Sensitive Baconian Cipher, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Origen = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Bin = List.Transform(
{0 .. 25},
(x) =>
try
List.Transform(
List.Reverse({0 .. Number.RoundDown(Number.Log(x, 2))}),
(y) => Number.RoundDown(Number.Mod(x / Number.Power(2, y), 2))
)
otherwise
{0}
),
Process = List.Transform(
Bin,
each
let
a = Text.PadStart(Text.Combine(List.Transform(_, Text.From), ""), 5, "0"),
b = Text.Combine(
List.Accumulate(
Text.ToList(a),
{},
(s, c) => s & List.ReplaceMatchingItems({c}, List.Zip({{"0", "1"}, {"a", "b"}}))
),
""
)
in
b
),
ZipAa = List.Zip(
{
{"a" .. "z", "A" .. "Z"},
List.Transform(Process, each "a" & _) & List.Transform(Process, each "b" & _)
}
),
Sol = Table.AddColumn(
Origen,
"Answer",
each
let
a = Text.ToList([Words]),
b = List.Transform(
{0 .. List.Count(a) - 1},
each List.ReplaceMatchingItems({a{_}}, ZipAa){0}
)
in
Text.Combine(b)
)
in
SolSolving the challenge of Case-Sensitive Baconian Cipher with Excel
Excel solution 1 for Case-Sensitive Baconian Cipher, proposed by Bo Rydobon 🇹🇭:
=MAP(
A2:A7,
LAMBDA(
a,
LET(
c,
CODE(
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
)
)-1,
CONCAT(
CHAR(
97+MID(
N(
c<96
)&RIGHT(
BASE(
c,
2
),
5
),
SEQUENCE(
,
6
),
1
)
)
)
)
)
)Excel solution 2 for Case-Sensitive Baconian Cipher, proposed by Rick Rothstein:
=MAP(
A2:A7,
LAMBDA(
w,
CONCAT(
SUBSTITUTE(
SUBSTITUTE(
DEC2BIN(
CODE(
MID(
LOWER(
w
),
SEQUENCE(
LEN(
w
)
),
1
)
)-97,
5
),
0,
"a"
),
1,
"b"
)
)
)
)Excel solution 3 for Case-Sensitive Baconian Cipher, proposed by John V.:
=MAP(
A2:A7,
LAMBDA(
x,
LET(
m,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
u,
UPPER(
m
),
SUBSTITUTE(
SUBSTITUTE(
CONCAT(
--EXACT(
m,
u
)&BASE(
CODE(
u
)-65,
2,
5
)
),
0,
"a"
),
1,
"b"
)
)
)
)Excel solution 4 for Case-Sensitive Baconian Cipher, proposed by محمد حلمي:
=MAP(
A2:A7,
LAMBDA(
a,
CONCAT(
TOCOL(
IFS(
EXACT(
CHAR(
TOCOL(
ROW(
65:90
)+{32,
0}
)
),
MID(
a,
SEQUENCE(
,
LEN(
a
)
),
1
)
),
SUBSTITUTE(
SUBSTITUTE(
TOCOL(
{0,
1}&BASE(
SEQUENCE(
26,
,
0
),
2,
5
)
),
0,
"a"
),
1,
"b"
)
),
2,
1
)
)
)
)Excel solution 5 for Case-Sensitive Baconian Cipher, proposed by محمد حلمي:
=MAP(
A2:A7,
LAMBDA(
a,
CONCAT(
IF(
-MID(
XLOOKUP(
CODE(
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
)
),
TOROW(
ROW(
65:90
)+{32,
0}
),
TOROW(
{0,
1}&BASE(
SEQUENCE(
26,
,
0
),
2,
5
)
)
),
SEQUENCE(
,
6
),
1
),
"b",
"a"
)
)
)
)Excel solution 6 for Case-Sensitive Baconian Cipher, proposed by Kris Jaganah:
=MAP(
A2:A7,
LAMBDA(
x,
LET(
a,
SEQUENCE(
26,
,
97
),
b,
CHAR(
a
),
c,
a-97,
d,
TEXT(
DEC2BIN(
c
),
"00000"
),
e,
SUBSTITUTE(
SUBSTITUTE(
d,
"0",
"a"
),
"1",
"b"
),
f,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
CONCAT(
IF(
--EXACT(
f,
UPPER(
f
)
),
"b",
"a"
)&XLOOKUP(
f,
b,
e
)
)
)
)
)Excel solution 7 for Case-Sensitive Baconian Cipher, proposed by Julian Poeltl:
=MAP(
A2:A7,
LAMBDA(
W,
LET(
SP,
MID(
W,
SEQUENCE(
LEN(
W
)
),
1
),
P,
IF(
EXACT(
UPPER(
SP
),
SP
),
"b",
"a"
),
CONCAT(
P&SUBSTITUTE(
SUBSTITUTE(
BASE(
CODE(
LOWER(
SP
)
)-97,
2,
5
),
0,
"a"
),
1,
"b"
)
)
)
)
)Excel solution 8 for Case-Sensitive Baconian Cipher, proposed by Timothée BLIOT:
=MAP(
A2:A7,
LAMBDA(
z,
LET(
A,
MID(
z,
SEQUENCE(
LEN(
z
)
),
1
),
B,
BASE(
CODE(
LOWER(
A
)
)-97,
2,
5
),
D,
--EXACT(
A,
UPPER(
A
)
),
CONCAT(
SUBSTITUTE(
SUBSTITUTE(
BYROW(
HSTACK(
D,
B
),
LAMBDA(
x,
CONCAT(
x
)
)
),
"0",
"a"
),
"1",
"b"
)
)
)
)
)Excel solution 9 for Case-Sensitive Baconian Cipher, proposed by Hussein SATOUR:
=MAP(
A2:A7,
LAMBDA(
x,
LET(
a,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
b,
CHAR(
SEQUENCE(
26,
,
97
)
),
c,
DEC2BIN(
ROW(
1:26
)-1,
5
),
CONCAT(
IF(
EXACT(
a,
UPPER(
a
)
),
"b",
"a"
) & SUBSTITUTE(
SUBSTITUTE(
XLOOKUP(
a,
b,
c
),
0,
"a"
),
1,
"b"
)
)
)
)
)Excel solution 10 for Case-Sensitive Baconian Cipher, proposed by Oscar Mendez Roca Farell:
=MAP(
A2:A7,
LAMBDA(
a,
LET(
_w,
CODE(
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
)
),
_c,
IF(
_w>90,
97,
65
),
CONCAT(
SUBSTITUTE(
SUBSTITUTE(
ABS(
SIGN(
_c-97
)
)&BASE(
_w-_c,
2,
5
&),
0,
"a"
),
1,
"b"
)
)
)
)
)Excel solution 11 for Case-Sensitive Baconian Cipher, proposed by Sunny Baggu:
=MAP(
A2:A7,
LAMBDA(
e,
LET(
_t,
CHAR(
SEQUENCE(
26,
,
97
)
),
_num,
SEQUENCE(
26,
,
0
),
_m,
MID(
e,
SEQUENCE(
LEN(
e
)
),
1
),
_mnum,
XLOOKUP(
_m,
_t,
_num
),
_mbin,
MAP(
_mnum,
LAMBDA(
a,
TEXT(
DEC2BIN(
a
),
"00000"
)
)
),
_UL,
IF(
CODE(
_m
) < 97,
"b",
"a"
),
_let,
MAP(
_mbin,
LAMBDA(
x,
SUBSTITUTE(
SUBSTITUTE(
x,
0,
"a"
),
1,
"b"
)
)
),
_flet,
MAP(
_UL,
_let,
LAMBDA(
a,
b,
TEXTJOIN(
,
,
a,
b
)
)
),
CONCAT(
_flet
)
)
)
)Excel solution 12 for Case-Sensitive Baconian Cipher, proposed by Pieter de Bruijn:
=MAP(
A2:A7,
LAMBDA(
x,
SUBSTITUTE(
SUBSTITUTE(
CONCAT(
DEC2BIN(
CODE(
MID(
UPPER(
x
),
SEQUENCE(
LEN(
x
)
),
1
)
)-65,
5
)
),
0,
"a"
),
1,
"b"
)
)
)Excel solution 13 for Case-Sensitive Baconian Cipher, proposed by Guillermo Arroyo:
=MAP(
A2:A7,
LAMBDA(
a,
LET(
u,
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
CONCAT(
IF(
CODE(
u
)<97,
"b",
"a"
)&SUBSTITUTE(
SUBSTITUTE(
BASE(
CODE(
LOWER(
u
)
)-97,
2,
5
),
"1",
"b"
),
"0",
"a"
)
)
)
)
)Excel solution 14 for Case-Sensitive Baconian Cipher, proposed by Daniel Garzia:
=MAP(
A2:A7,
LAMBDA(
x,
LET(
m,
CODE(
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)
),
CONCAT(
IF(
m<91,
"b",
"a"
)&SUBSTITUTE(
SUBSTITUTE(
MAP(
m-IF(
m<91,
65,
97
),
LAMBDA(
r,
DEC2BIN(
r,
5
)
)
),
0,
"a"
),
1,
"b"
)
)
)
)
)Excel solution 15 for Case-Sensitive Baconian Cipher, proposed by Quadri Olayinka Atharu:
=MAP(A2:A7,
LAMBDA(x,
LET(_char,
CHAR(
SEQUENCE(
26,
,
65
)
),
_spl,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
_lookup,
BASE((XMATCH(
_spl,
_char
))-1,
2,
5),
_codes,
SUBSTITUTE(
SUBSTITUTE(
_lookup,
0,
"a"
),
1,
"b"
),
_prefix,
MAP(
_spl,
LAMBDA(
x,
IF(
ISNUMBER(
TOCOL(
FIND(
x,
_char
),
2
)
),
"b",
"a"
)
)
),
_r,
CONCAT(
_prefix&_codes
),
_r)))Excel solution 16 for Case-Sensitive Baconian Cipher, proposed by Quadri Olayinka Atharu:
=DROP(REDUCE("",
A2:A7,
LAMBDA(a,
x,
LET(_char,
CHAR(
SEQUENCE(
26,
,
65
)
),
_spl,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
_lookup,
DEC2BIN((XMATCH(
_spl,
_char
))-1,
5),
_codes,
SUBSTITUTE(
SUBSTITUTE(
_lookup,
0,
"a"
),
1,
"b"
),
_prefix,
MAP(
_spl,
LAMBDA(
x,
IF(
ISNUMBER(
TOCOL(
FIND(
x,
_char
),
2
)
),
"b",
"a"
)
)
),
_r,
CONCAT(
_prefix&_codes
),
VSTACK(
a,
_r
)))),
1)Excel solution 17 for Case-Sensitive Baconian Cipher, proposed by Hussain Ali Nasser:
=BYROW(
A2:A7,
LAMBDA(
_string,
LET(
_split,
MID(
_string,
SEQUENCE(
LEN(
_string
)
),
1
),
_codes,
CODE(
_split
),
_case,
IF(
_codes>=97,
{97,
"a"},
{65,
"b"}
),
_adj,
_codes-TAKE(
_case,
,
1
),
_binary,
DEC2BIN(
_adj,
5
),
_prefix,
TAKE(
_case,
,
-1
)&_binary,
SUBSTITUTE(
SUBSTITUTE(
CONCAT(
_prefix
),
0,
"a"
),
1,
"b"
)
)
)
)&&