Find the Decrypted words corresponding to Encrypted words . The encryption (Baconian Cipher) was done using following logic (this is case insensitive) – A is 0, B is 1, C is 2…..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 reps. Hence, these will become aaaaa, aaaab, aaaba…..bbaab. Now, to encypt any word, you can replace the letters in the string with these text representations. Hence, Excel would become aabaababbbaaabaaabaaababb.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 268
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Decrypt Baconian Cipher Text with Power Query
Power Query solution 1 for Decrypt Baconian Cipher Text, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Ans = Table.TransformRows(
Source,
each Text.Proper(
Text.Combine(
List.Transform(
List.Split(Text.ToList([Encrypted Text]), 5),
each Character.FromNumber(
List.Sum(
List.Transform(
List.Positions(_),
(n) => Number.From(_{n} = "b") * Number.Power(2, 4 - n)
)
)
+ 65
)
)
)
)
)
in
AnsPower Query solution 2 for Decrypt Baconian Cipher Text, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content][Encrypted Text],
Decrypt = (x) =>
let
r = List.Transform(List.Reverse(Text.ToList(x)), each Character.ToNumber(_) - 97)
in
Character.FromNumber(
97 + List.Sum(List.Transform(List.Positions(r), each r{_} * Number.Power(2, _)))
),
S = List.Transform(
Source,
each Text.Proper(
List.Accumulate(
{0 .. Text.Length(_) / 5 - 1},
"",
(s, c) => s & Decrypt(Text.Middle(_, c * 5, 5))
)
)
)
in
SPower Query solution 3 for Decrypt Baconian Cipher Text, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.AddColumn(
Source,
"Answer",
each
let
a = List.Split(Text.ToList([Encrypted Text]), 5),
b = List.Zip({{"a", "b"}, {0, 1}}),
c = List.Transform(
a,
each Text.Combine(List.Transform(List.ReplaceMatchingItems(_, b), (x) => Text.From(x)))
),
d = 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}
),
e = List.Zip(
{
List.Transform(
d,
each Text.PadStart(Text.Combine(List.Transform(_, Text.From)), 5, "0")
),
{"a" .. "z"}
}
),
f = Text.Proper(Text.Combine(List.ReplaceMatchingItems(c, e)))
in
f
)[[Answer]]
in
SolPower Query solution 4 for Decrypt Baconian Cipher Text, proposed by Venkata Rajesh:
let
Source = Data,
Output = Table.AddColumn(
Source,
"Expected",
each [
x = Text.ToList([Encrypted Text]),
y = List.Count(x),
z = Text.Proper(
Text.Combine(
List.Transform(
List.Split(
List.Transform(
{0 .. y - 1},
each (if x{_} = "b" then 1 else 0)
* Number.Power(2, List.Repeat(List.Reverse({0 .. 4}), y / 5){_})
),
5
),
each {"A" .. "Z"}{List.Sum(_)}
)
)
)
][z]
)
in
OutputSolving the challenge of Decrypt Baconian Cipher Text with Excel
Excel solution 1 for Decrypt Baconian Cipher Text, proposed by Bo Rydobon 🇹🇭:
=MAP(A2:A7,LAMBDA(a,PROPER(CONCAT(CHAR(MMULT(N(MID(a,SEQUENCE(LEN(a)/5,5),1)="b"),32/2^SEQUENCE(5))+65)))))Excel solution 2 for Decrypt Baconian Cipher Text, proposed by Bo Rydobon 🇹🇭:
=MAP(
SUBSTITUTE(
SUBSTITUTE(
A2:A7,
"a",
0
),
"b",
1
),
LAMBDA(
a,
PROPER(
CONCAT(
CHAR(
DECIMAL(
MID(
a,
SEQUENCE(
LEN(
a
)/5,
,
,
5
),
5
),
2
)+65
)
)
)
)
)Excel solution 3 for Decrypt Baconian Cipher Text, proposed by Rick Rothstein:
=MAP(
A2:A7,
LAMBDA(
x,
PROPER(
CONCAT(
CHAR(
BIN2DEC(
MID(
SUBSTITUTE(
SUBSTITUTE(
x,
"a",
0
),
"b",
1
),
SEQUENCE(
LEN(
x
)/5,
,
,
5
),
5
)
)+65
)
)
)
)
)Excel solution 4 for Decrypt Baconian Cipher Text, proposed by John V.:
=MAP(
A2:A7,
LAMBDA(
x,
PROPER(
CONCAT(
CHAR(
65+BIN2DEC(
MID(
SUBSTITUTE(
SUBSTITUTE(
x,
"a",
0
),
"b",
1
),
SEQUENCE(
LEN(
x
)/5,
,
,
5
),
5
)
)
)
)
)
)
)
✅=MAP(
A2:A7,
LAMBDA(
x,
PROPER(
CONCAT(
CHAR(
65+MMULT(
N(
MID(
x,
SEQUENCE(
LEN(
x
)/5,
5
),
1
)="b"
),
{16;8;4;2;1}
)
)
)
)
)
)Excel solution 5 for Decrypt Baconian Cipher Text, proposed by محمد حلمي:
=MAP(
A2:A7,
LAMBDA(
a,
LET(
r,
ROW(
1:26
),
PROPER(
CONCAT(
XLOOKUP(
MID(
a,
r*5-4,
5
),
SUBSTITUTE(
SUBSTITUTE(
BASE(
r-1,
2,
5
),
1,
"b"
),
0,
"a"
),
CHAR(
r+96
),
""
)
)
)
)
)
)Excel solution 6 for Decrypt Baconian Cipher Text, proposed by Kris Jaganah:
=MAP(
A2:A7,
LAMBDA(
y,
LET(
a,
SEQUENCE(
LEN(
y
)
),
b,
WRAPROWS(
MID(
y,
a,
1
),
5
),
PROPER(
CONCAT(
BYROW(
IF(
b="a",
0,
1
),
LAMBDA(
x,
CHAR(
65+BIN2DEC(
CONCAT(
x
)
)
)
)
)
)
)
)
)
)Excel solution 7 for Decrypt Baconian Cipher Text, proposed by Aditya Kumar Darak 🇮🇳:
=MAP(
A2:A7,
LAMBDA(
a,
LET(
splt,
MID(
a,
SEQUENCE(
LEN(
a
) / 5,
,
1,
5
),
5
),
sub,
SUBSTITUTE(
SUBSTITUTE(
splt,
"b",
1
),
"a",
0
),
dec,
BIN2DEC(
sub
),
r,
PROPER(
CONCAT(
CHAR(
dec + 97
)
)
),
r
)
)
)Excel solution 8 for Decrypt Baconian Cipher Text, proposed by Timothée BLIOT:
=MAP(A2:A7,
LAMBDA(z,
PROPER(CONCAT(CHAR(BIN2DEC(SUBSTITUTE(SUBSTITUTE(MID(z,
1+(SEQUENCE(
LEN(
z
)/5
)-1)*5,
5),
"a",
"0"),
"b",
"1"))+65)))))Excel solution 9 for Decrypt Baconian Cipher Text, proposed by Oscar Mendez Roca Farell:
=MAP(A2:A7, LAMBDA(a, PROPER(CONCAT(CHAR(BIN2DEC(BYCOL(CODE( WRAPCOLS(MID(a, SEQUENCE(LEN(a)), 1), 5))-97, LAMBDA(c, CONCAT(c))))+97)))))Excel solution 10 for Decrypt Baconian Cipher Text, proposed by Sunny Baggu:
=MAP(
A2:A7,
LAMBDA(
t,
LET(
_list,
CHAR(
SEQUENCE(
26,
,
97
)
),
_num,
SEQUENCE(
26,
,
0
),
_bin,
MAP(
_num,
LAMBDA(
a,
RIGHT(
REPT(
0,
4
) & DEC2BIN(
a
),
5
)
)
),
_ab,
MAP(
_bin,
LAMBDA(
x,
SUBSTITUTE(
SUBSTITUTE(
x,
"0",
"a"
),
"1",
"b"
)
)
),
_text,
BYROW(
WRAPROWS(
MID(
t,
SEQUENCE(
LEN(
t
)
),
1
),
5
),
LAMBDA(
a,
CONCAT(
a
)
)
),
PROPER(
CONCAT(
XLOOKUP(
_text,
_ab,
_list
)
)
)
)
)
)Excel solution 11 for Decrypt Baconian Cipher Text, proposed by LEONARD OCHEA 🇷🇴:
=MAP(
A2:A7,
LAMBDA(
x,
PROPER(
CONCAT(
CHAR(
65+BIN2DEC(
MID(
SUBSTITUTE(
SUBSTITUTE(
x,
"a",
0
),
"b",
1
),
SEQUENCE(
LEN(
x
)/5,
,
1,
5
),
5
)
)
)
)
)
)
)Excel solution 12 for Decrypt Baconian Cipher Text, proposed by LEONARD OCHEA 🇷🇴:
=MID( A2 , SEQUENCE(LEN(A2)) ,1 )Excel solution 13 for Decrypt Baconian Cipher Text, proposed by Abdallah Ally:
=MAP(
A2:A7,
LAMBDA(
v,
LET(
a,
CHAR(
SEQUENCE(
26,
& ,
97
)
),
b,
DEC2BIN(
SEQUENCE(
26,
,
0
),
5
),
c,
SUBSTITUTE(
SUBSTITUTE(
b,
0,
"a"
),
1,
"b"
),
PROPER(
REDUCE(
"",
SEQUENCE(
LEN(
v
)/5,
,
1,
5
),
LAMBDA(
x,
y,
CONCAT(
x,
XLOOKUP(
MID(
v,
y,
5
),
c,
a,
""
)
)
)
)
)
)
)
)Excel solution 14 for Decrypt Baconian Cipher Text, proposed by Charles Roldan:
=LET(f,
(LAMBDA(u,
LAMBDA(x,
IF(LEN(
x
),
(RIGHT(
x
) = "b") + 2 * u(
u
)(REPLACE(
x,
LEN(
x
),
1,
)),
)))),
g,
(LAMBDA(u,
LAMBDA(x,
IF(LEN(
x
),
CHAR(f(
f
)(LEFT(
x,
5
)) + CODE(
"a"
)) & u(
u
)(REPLACE(
x,
1,
5,
)),
)))),
PROPER(
MAP(
A2:A7,
g(
g
)
)
))Excel solution 15 for Decrypt Baconian Cipher Text, proposed by Julien Lacaze:
=PROPER(
MAP(
A2:A7,
LAMBDA(
a,
CONCAT(
CHAR(
65+BIN2DEC(
MID(
SUBSTITUTE(
SUBSTITUTE(
a,
"b",
"1"
),
"a",
"0"
),
SEQUENCE(
LEN(
a
)/5,
,
,
5
),
5
)
)
)
)
)
)
)Excel solution 16 for Decrypt Baconian Cipher Text, proposed by Pieter de Bruijn:
=MAP(
A2:A7,
LAMBDA(
e,
PROPER(
CONCAT(
CHAR(
65+DECIMAL(
MID(
SUBSTITUTE(
SUBSTITUTE(
e,
"a",
0
),
"b",
1
),
SEQUENCE(
,
LEN(
e
)/5,
,
5
),
5
),
2
)
)
)
)
)
)
or 2 char shorter:
=MAP(
A2:A7,
LAMBDA(
e,
PROPER(
CONCAT(
CHAR(
65+BIN2DEC(
MID(
SUBSTITUTE(
SUBSTITUTE(
e,
"a",
0
),
"b",
1
),
SEQUENCE(
,
LEN(
e
)/5,
,
5
),
5
)
)
)
)
)
)
)Excel solution 17 for Decrypt Baconian Cipher Text, proposed by Ziad A.:
=MAP(
A2:A6,
LAMBDA(
a,
JOIN(
,
INDEX(
CHAR(
65+BIN2DEC(
REGEXEXTRACT(
SUBSTITUTE(
SUBSTITUTE(
a,
"a",
0
),
"b",
1
),
REPT(
"(.{5})",
LEN(
a
)/5
)
)
)
)
)
)
)
)Excel solution 18 for Decrypt Baconian Cipher Text, proposed by Giorgi Goderdzishvili:
=MAP(
A2:A7,
LAMBDA(
x,
LET(
wrd,
UPPER(
x
),
ln,
LEN(
wrd
),
chr,
MID(
wrd,
SEQUENCE(
,
ln
),
1
),
cd,
CODE(
chr
)-65,
fin,
CONCAT(
BASE(
cd,
2,
5
)
),
rep,
SUBSTITUTE(
SUBSTITUTE(
fin,
0,
"a"
),
1,
"b"
),
rep
)
)
)Excel solution 19 for Decrypt Baconian Cipher Text, proposed by Daniel Garzia:
=MAP(
A2:A7,
LAMBDA(
x,
PROPER(
CONCAT(
BYROW(
WRAPROWS(
IF(
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)="a",
,
1
),
5
),
LAMBDA(
x,
CHAR(
97+BIN2DEC(
CONCAT(
x
)
)
)
)
)
)
)
)
)Excel solution 20 for Decrypt Baconian Cipher Text, proposed by Quadri Olayinka Atharu:
=MAP(
A2:A7,
LAMBDA(encrypted_text,
LET(
binary_code,
SUBSTITUTE(SUBSTITUTE(encrypted_text, "a", 0), "b", 1),
binary_groups,
MID(binary_code, SEQUENCE(LEN(binary_code) / 5, , , 5), 5),
decrypted_chars,
PROPER(CONCAT(CHAR(BIN2DEC(binary_groups) + 65))),
decrypted_chars)))Excel solution 21 for Decrypt Baconian Cipher Text, proposed by Quadri Olayinka Atharu:
=MAP(A2:A7,LAMBDA(et,
LET(bin,SUBSTITUTE(SUBSTITUTE(et,"a",0),"b","1"),
s,MID(bin,SEQUENCE(LEN(bin)/5,,,5),5),
PROPER(CONCAT(CHAR(BIN2DEC(--s)+65))))))Excel solution 22 for Decrypt Baconian Cipher Text, proposed by Diarmuid Early:
=MAP(
A2:A7,
LAMBDA(
input,
LET(
nums,
IF(
MID(
input,
SEQUENCE(
LEN(
input
)
),
1
)="a",
0,
1
),
codes,
BYROW(
WRAPROWS(
nums,
5
),
LAMBDA(
rw,
CONCAT(
rw
)
)
),
ltrs,
CHAR(
BIN2DEC(
codes
)+65
),
out,
PROPER(
CONCAT(
ltrs
)
),
out
)
)
)Excel solution 23 for Decrypt Baconian Cipher Text, proposed by Md Ismail Hosen:
=LET(
EncryptedTexts,
A2:A7,
GROUP_COUNT,
5,
ZeroTo25,
SEQUENCE(
26,
,
0
),
BinToCharExpr,
SUBSTITUTE(
SUBSTITUTE(
DEC2BIN(
ZeroTo25,
GROUP_COUNT
),
0,
"a"
),
1,
"b"
),
Chars,
CHAR(
CODE(
"a"
) + ZeroTo25
),
fx_One,
LAMBDA(
OneEncryptedText,
LET(
FiveCharGroup,
MID(
OneEncryptedText,
SEQUENCE(
LEN(
OneEncryptedText
) / GROUP_COUNT,
,
1,
GROUP_COUNT
),
GROUP_COUNT
),
PROPER(
CONCAT(
MAP(
FiveCharGroup,
LAMBDA(
a,
XLOOKUP(
a,
BinToCharExpr,
Chars
)
)
)
)
)
)
),
MAP(
EncryptedTexts,
fx_One
)
)Excel solution 24 for Decrypt Baconian Cipher Text, proposed by Mungunbayar Bat-Ochir:
=LET(
input;SUBSTITUTE(SUBSTITUTE(A2;"a";0);"b";1);
bins;MID(input;SEQUENCE(LEN(input)/5;;;5);5);
result;PROPER(CONCAT(UNICHAR(BIN2DEC(bins)+65)));
result
)Excel solution 25 for Decrypt Baconian Cipher Text, proposed by Henriette Hamer:
=MAP(
A2:A7;
LAMBDA(
_list;
PROPER(
CONCAT(
LET(
_text;
_list;
_len;
LEN(
_text
);
_char;
_len/5;
_seq;
SEQUENCE(
;
_char;
1;
5
);
CHAR(
BIN2DEC(
MID(
SUBSTITUTE(
SUBSTITUTE(
_text;
"a";
0
);
"b";
"1"
);
_seq;
5
)
)+65
)
)
)
)
)
)Excel solution 26 for Decrypt Baconian Cipher Text, proposed by Hussain Ali Nasser:
=PROPER(MAP(A2:A7,LAMBDA(e,CONCAT(BYROW(WRAPROWS(IF(MID(e,SEQUENCE(LEN(e)),1)="b",1,0),5),LAMBDA(r,CHAR(BIN2DEC(--CONCAT(r))+65)))))))Excel solution 27 for Decrypt Baconian Cipher Text, proposed by Hussain Ali Nasser:
=PROPER(
MAP(
A2:A7,
LAMBDA(
e,
CONCAT(
BYROW(
WRAPROWS(
IF(
MID(
e,
SEQUENCE(
LEN(
e
)
),
1
)="b",
1,
0
),
5
),
LAMBDA(
r,
CHAR(
MOD(
BIN2DEC(
--CONCAT(
r
)
),
97
)+97
)
)
)
)
)
)
)Excel solution 28 for Decrypt Baconian Cipher Text, proposed by Xavier Y.:
=proper(
Decip(
A2
)
) etc.
=LAMBDA(
a,
IF(
LEN(
a
)<=5,
CHAR(
97+BIN2DEC(
SUBSTITUTE(
SUBSTITUTE(
UPPER(
a
),
"A",
"0"
),
"B",
"1"
)
)
),
CHAR(
97+BIN2DEC(
SUBSTITUTE(
SUBSTITUTE(
UPPER(
LEFT(
a,
5
)
),
"A",
"0"
),
"B",
"1"
)
)
)&Decip(
RIGHT(
a,
LEN(
a
)-5
)
)
)
)Solving the challenge of Decrypt Baconian Cipher Text with Excel VBA
Excel VBA solution 1 for Decrypt Baconian Cipher Text, proposed by Rick Rothstein:
=DeBaconian(A2:A7)
Function DeBaconian(R As Range) as Variant
Dim X As Long, N As Long, T As Long
Dim S As String, Cell As Range
ReDim V(1 To R.Count, 1 To 1)
For Each Cell In R
N = N + 1
S = Cell.Value
For X = 1 To Len(S)
Mid(S, X) = 0 - (Mid(S, X, 1) = "b")
Next
For X = 1 To Len(S)& Step 5
T = Application.Bin2Dec(Mid(S, X, 5))
V(N, 1) = V(N, 1) & Chr(T + 65 - 32 * (X > 1))
Next
Next
DeBaconian = V
End Function
Excel VBA solution 2 for Decrypt Baconian Cipher Text, proposed by Nicolas Micot:
VBA solution:
Function f_baconCypher(ByVal texte As String) As String
Dim codeDebut As Integer, decalage As Integer, val As Integer
Dim resultat As String
codeDebut = Asc("a")
For i = 1 To Len(texte) Step 5
decalage = 0
val = 1
For x = i + 4 To i Step -1
If Mid(texte, x, 1) = "b" Then
decalage = decalage + val
End If
val = val * 2
Next x
If resultat = "" Then
resultat = UCase(Chr(codeDebut + decalage))
Else
resultat = resultat & Chr(codeDebut + decalage)
End If
Next i
f_baconCypher = resultat
End Function
&