Encrypt the given words with following rules 1. Make length of key = length of words + 1. If key is shorter than words, repeat the key to match the length. 2. Find the position of individual alphabets in Words in English alphabets table which is a = 1, b = 2….z = 26. 3. Prefix, insert and append those many random printable characters (character codes 32 to 132 are printable) as per positions derived in step 2. Since we are inserting random characters, hence answers wouldn’t match. Ex. Word = watch and Key = abcd Repeat key till length of key = length of word + 1 => abcdab (length of word is 5, hence length of key has to be 6). Position of abcdab in English alphabets is 1, 2, 3, 4, 1, 2 Prefix 1 character before w. Now insert 2 printable characters between w and a, 3 printable characters between a and t, 4 printable characters between t and c and 1 between c & h. Append 2 printable characters after h.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 633
Challenge Difficulty: ⭐️⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Encrypt Words with Random Characters with Power Query
Power Query solution 1 for Encrypt Words with Random Characters, proposed by Mihai Radu O:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
s = Table.AddColumn(
Source,
"r",
each [
k = [key],
w = [Words],
tl = Text.Length,
ttl = Text.ToList,
lt = List.Transform,
tc = Text.Combine,
lw = tl(w) + 1,
lk = tl(k),
_k = Text.Repeat(k, Number.IntegerDivide(lw, lk)) & Text.Start(k, Number.Mod(lw, lk)),
a = lt(
ttl(_k),
(x) =>
tc(
lt(
{1 .. List.PositionOf({"a" .. "z"}, x) + 1},
each Character.FromNumber(Int64.From(Number.RandomBetween(32, 132)))
)
)
),
b = ttl(w),
c = List.First(a),
d = c & tc(lt(List.Zip({b, List.Skip(a)}), (x) => x{0} & x{1}))
][d]
)
in
sSolving the challenge of Encrypt Words with Random Characters with Excel
Excel solution 1 for Encrypt Words with Random Characters, proposed by Bo Rydobon 🇹🇭:
=MAP(A2:A11,B2:B11,LAMBDA(w,k,LET(s,SEQUENCE(LEN(w)+1),TEXTJOIN(MID(w,s,1),,MAP(s,LAMBDA(i,CONCAT(CHAR(RANDARRAY(MOD(CODE(MID(k,MOD(i-1,LEN(k))+1,1)),32),,32,132,1)))))))))Excel solution 2 for Encrypt Words with Random Characters, proposed by Rick Rothstein:
=MAP(
A2:A11,
B2:B11,
LAMBDA(
w,
k,
CONCAT(
TOROW(
HSTACK(
DROP(
REDUCE(
"",
CODE(
MID(
LEFT(
REPT(
k,
9
),
LEN(
w
)+1
),
SEQUENCE(
LEN(
w
)+1
),
1
)
)-96,
LAMBDA(
a,
x,
VSTACK(
a,
CONCAT(
CHAR(
RANDARRAY(
,
x,
32,
132,
1
)
)
)
)
)
),
1
),
MID(
w,
SEQUENCE(
LEN(
w
)
),
1
)
),
2
)
)
)
)
Note: I assumed the length of the word would always be less than 9 times the length of the key. If the word could be longer,
then just increase the 9 in my formula (it can be any large number)Excel solution 3 for Encrypt Words with Random Characters, proposed by John V.:
=MAP(A2:A11,B2:B11,LAMBDA(w,k,LET(s,SEQUENCE(1+LEN(w)),CONCAT(MAP(CODE(MID(REPT(k,9),s,1))-96,LAMBDA(x,CONCAT(CHAR(RANDARRAY(x,,32,132,1)))))&MID(w,s,1)))))Excel solution 4 for Encrypt Words with Random Characters, proposed by Timothée BLIOT:
=MAP(
A2:A11,
B2:B11,
LAMBDA(
i,
j,
LET(
A,
LEN(
i
),
B,
MID(
i,
SEQUENCE(
A
),
1
),
C,
LEN(
j
),
D,
MID(
j,
SEQUENCE(
C
),
1
),
CONCAT(
MAP(
TOCOL(
HSTACK(
CODE(
INDEX(
D,
MOD(
SEQUENCE(
A+1
)-1,
C
)+1
)
)-96,
B
),
3
),
LAMBDA(
x,
IF(
ISNUMBER(
x
),
CONCAT(
MAP(
SEQUENCE(
x
),
LAMBDA(
y,
CHAR(
INT(
RAND()*100
)+32
)
)
)
),
x
)
)
)
)
)
)
)Excel solution 5 for Encrypt Words with Random Characters, proposed by Md. Zohurul Islam:
=MAP(
A2:A11,
B2:B11,
LAMBDA(
p,
q,
LET(
sq,
SEQUENCE(
LEN(
p
)+1
),
a,
MID(
p,
sq,
1
),
b,
MAP(
sq,
LAMBDA(
x,
CODE(
MID(
q,
MOD(
x-1,
LEN(
q
)
)+1,
1
)
)
)
),
c,
MOD(
b,
32
),
d,
MAP(
c,
LAMBDA(
y,
CONCAT(
CHAR(
RANDARRAY(
y,
,
32,
132,
1
)
)
)
)
),
e,
TEXTJOIN(
a,
,
d
),
e
)
)
)Excel solution 6 for Encrypt Words with Random Characters, proposed by Md. Zohurul Islam:
=MAP(A2:A11,
B2:B11,
LAMBDA(x,
y,
LET(
sq,
SEQUENCE(
26
),
ltr,
CHAR(
sq+96
),
p,
LEN(
x
),
q,
REPT(
y,
20
),
r,
LEFT(
q,
p+1
),
s,
MID(
r,
SEQUENCE(
LEN(
r
)
),
1
),
t,
XLOOKUP(
s,
ltr,
sq
),
u,
MID(
x,
SEQUENCE(
p
),
1
),
v,
MAP(
u,
DROP(
t,
1
),
LAMBDA(
x,
y,
TEXTJOIN(
",",
1,
x,
y
)
)
),
w,
VSTACK(
TAKE(
t,
1
),
v
),
z,
TEXTJOIN(
",",
,
w
),
a,
TEXTSPLIT(
z,
,
","
),
b,
MAP(
a,
LAMBDA(
x,
IF(
ISNUMBER(
--x
),
CONCAT(
CHAR(
RANDARRAY(
,
x,
32,
132,
1
)
)
),
x
)
)
),
d,
CONCAT(
b
),
d
)Excel solution 7 for Encrypt Words with Random Characters, proposed by Seokho MOON:
= encrypted_word.strip()
prefix_length = [ord(
c
) - ord(
"a"
) + 1 for c in key]
result = []
while encrypted_word:
for i in prefix_length:
if i < len(
encrypted_word
):
result.append(
encrypted_word[i]
)
encrypted_word = encrypted_word[i + 1 :]
else:
encrypted_word = ""
break
return "".join(
result
)Excel solution 8 for Encrypt Words with Random Characters, proposed by Andres Rojas Moncada:
=MAP(
A2:A11,
B2:B11,
LAMBDA(
p,
c,
LET(
s,
SEQUENCE(
LEN(
p
)+1
),
CONCAT(
MAP(
CODE(
MID(
c,
MOD(
s-1,
LEN(
c
)
)+1,
1
)
)-96,
LAMBDA(
v,
CONCAT(
CHAR(
RANDARRAY(
v,
,
32,
132,
1
)
)
)
)
)&MID(
p,
s,
1
)
)
)
)
)Excel solution 9 for Encrypt Words with Random Characters, proposed by Andres Rojas Moncada:
=MAP(
A2:A11,
B2:B11,
LAMBDA(
p,
c,
LET(
spa,
SEQUENCE(
LEN(
p
)+1
),
rll,
MAP(
CODE(
MID(
c,
MOD(
spa-1,
LEN(
c
)
)+1,
1
)
)-97+1,
LAMBDA(
val,
CONCAT(
CHAR(
RANDARRAY(
val,
,
32,
132,
1
)
)
)
)
),
CONCAT(
rll&MID(
p,
spa,
1
)
)
)
)
)Solving the challenge of Encrypt Words with Random Characters with Python
Python solution 1 for Encrypt Words with Random Characters, proposed by Konrad Gryczan, PhD:
import pandas as pd
import random
import string
path = "633 Encryption by Printable Characters.xlsx"
input = pd.read_excel(path, usecols="A:B", nrows=10)
def n_random_chars(n):
random_chars = ''.join(random.choices(string.printable, k=n))
return random_chars
def encode_word(word, key):
key = (key * (len(word) // len(key) + 1))[:len(word)]
key_num = [string.ascii_lowercase.index(k) + 1 for k in key]
return ''.join([w + n_random_chars(num) for w, num in zip(word, key_num)])
input['Sample_answer'] = input.apply(lambda row: encode_word(row['Words'], row['key']), axis=1)
print(input)
Solving the challenge of Encrypt Words with Random Characters with Python in Excel
Python in Excel solution 1 for Encrypt Words with Random Characters, proposed by Alejandro Campos:
import random, string
def encrypt_word(word, key):
ek = (key * ((len(word)+1)//len(key)+1))[:len(word)+1]
r = lambda c: ''.join(random.choices(string.printable[32:133], k=ord(c.lower()) - 96))
return ''.join(r(ek[i]) + word[i] if i < len(word) else r(ek[i]) for i in range(len(word)+1))
df = xl("A1:B11", headers=True)
df['Encrypted'] = df.apply(lambda row: encrypt_word(row['Words'], row['key']), axis=1)
df
Solving the challenge of Encrypt Words with Random Characters with R
R solution 1 for Encrypt Words with Random Characters, proposed by Konrad Gryczan, PhD:
_x000D_
&&