Pig Latin Decrypter – Decrypt the given encrypted text where encryption logic was following – A new encrypted word is created with 3 components – Component 1 – String having all alphabets from first vowel occurrence till end of the word Component 2 – String having all alphabets before first vowel occurrence Component 3 – String “ay” Anything other than the English alphabet will be left as it is.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 511
Challenge Difficulty: ⭐️⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Decrypt Pig Latin Words with Power Query
Power Query solution 1 for Decrypt Pig Latin Words, proposed by Ahmed Ariem:
let
f= (w)=> [a = Splitter.SplitTextByAnyDelimiter({"ay"})(w),
b= List.Select(a,(x)=> x<>""),
c = List.Transform(b, (x)=> Text.Remove(x, {"&",","," "})),
d = Text.Combine( List.Transform(c, (x)=> Text.End(x,1)& Text.Range(x,0,Text.Length(x)-1))," ")][d],
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PY5LDsMgDAWvQllHVdRFtz1ImoVLHKAhpuKjyLevSZtsEMx7YzMMGslWTx9gPXaDjnGywCom8mTXP6zrK2EIcD4/Er6l9qx9f7srqNYJCU2kBfnwHNBSGqwM3KmY5V6Nk/i651SSN1DQC4dSMJ3fkFUKk8fclDnKwcXlY27cXDPyts9O3Mq4IE0CHj8/BNMqrWBKTFPbD7nMwBc9jl8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Encrypted Text" = _t]),
final = Table.TransformColumns(Source,{"Encrypted Text",f})
in
final
---
attached file
https://1drv.ms/x/s!AiUZ0Ws7G26Rj1mSz0dDzbrfvCiB?e=9V6YlD
Solving the challenge of Decrypt Pig Latin Words with Excel
Excel solution 1 for Decrypt Pig Latin Words, proposed by Bo Rydobon 🇹🇭:
=MAP(A2:A10,LAMBDA(a,LET(b,TEXTSPLIT(a," "),f,FIND("ay",b),c,MID(b,f-1,1),v,"aeiou",y,ISERR(FIND(c,v)),
TEXTJOIN(" ",,IFERROR(IF(ISNUMBER(FIND(LEFT(b),v)),REPT(c,y)&REPLACE(b,f-y,3,),REPLACE(b,f,3,)),b)))))Excel solution 2 for Decrypt Pig Latin Words, proposed by Bo Rydobon 🇹🇭:
=MAP(A2:A10,LAMBDA(a,LET(b,TEXTSPLIT(a," "),f,FIND("ay",b)-1,c,MID(b,f,1),y,ISERR(FIND(c,"aeiou")),
TEXTJOIN(" ",,IFERROR(REPT(c,y)&REPLACE(b,f+1-y,3,),b)))))Excel solution 3 for Decrypt Pig Latin Words, proposed by Bo Rydobon 🇹🇭:
=REGEXREPLACE(
A2:A10,
"b(([aeiou]w*?)([^aeiou]?)|(w+))ayb",
"$3$2$4"
)Excel solution 4 for Decrypt Pig Latin Words, proposed by John V.:
=MAP(A2:A10,
LAMBDA(t,
LET(s,
SUBSTITUTE,
x,
TEXTSPLIT(
s(
t,
"ay",
),
" "
),
v,
"aeiou",
r,
RIGHT(
x
),
n,
LEN(
x
),
c,
r<"a",
z,
REPT(
r,
c
),
TEXTJOIN(" ",
,
IF(ISERR(
FIND(
r,
v
)
)*(1-ISERR(
FIND(
LEFT(
x
),
v
)
)),
s(
MID(
x&x,
n-c,
n
),
z,
)&z,
x)))))Excel solution 5 for Decrypt Pig Latin Words, proposed by محمد حلمي:
=MAP(SUBSTITUTE(A2:A10,"ay",),LAMBDA(a,LET(
d,TEXTSPLIT(a," "),r,RIGHT(d),i,LEN(d),
e,FIND(r,"&!,?."),TEXTJOIN(" ",,IFERROR(IF(e,LEFT(
RIGHT(d,2))&LEFT(d,i-2)&r),r&LEFT(d,i-1))))))Excel solution 6 for Decrypt Pig Latin Words, proposed by Julian Poeltl:
=MAP(A2:A10,LAMBDA(T,LET(SP,TEXTSPLIT(T," "),S,IF(CODE(RIGHT(SP))<97,RIGHT(SP,1),""),C,IF(CODE(RIGHT(SP))<97,LEFT(SP,LEN(SP)-1),SP),CC,LEFT(C,LEN(C)-2),TEXTJOIN(" ",,IFERROR(IF(ISNUMBER(SEARCH(RIGHT(CC,1),"aeiou")),CC,RIGHT(CC,1)&LEFT(CC,LEN(CC)-1)),"")&S))))Excel solution 7 for Decrypt Pig Latin Words, proposed by Timothée BLIOT:
=REGEXREPLACE(
A2:A10,
"b(([aeiou]?[a-z]*?)([^aeiou]?)|([a-z]+))(ay)b",
"$3$2"
)Excel solution 8 for Decrypt Pig Latin Words, proposed by Pieter de Bruijn:
=MAP(A2:A10,LAMBDA(a,LET(b,TEXTSPLIT(a," "),c,IFERROR(TEXTBEFORE(b,"ay",-1),b),d,IF(ISERR(FIND(RIGHT(c),"aeiou")),RIGHT(c)&LEFT(c,LEN(c)-1),c)&IFERROR(TEXTAFTER(b,"ay",-1),""),TEXTJOIN(" ",,d))))
This doesn't solve "thank", but this is not recognizable by formula as being correct spelled or not. (I tried pulling it through =TRANSLATE(), hoping it would error, but that keeps unrecognized words as is; otherwise that would've been an entrance).
I can of course say IF(RIGHT(c="th",...)Solving the challenge of Decrypt Pig Latin Words with Python
Python solution 1 for Decrypt Pig Latin Words, proposed by Konrad Gryczan, PhD:
from spellchecker import SpellChecker
import re
import pandas as pd
path = "511 Pig Latin Decrypter.xlsx"
input = pd.read_excel(path, usecols="A")
test = pd.read_excel(path, usecols="B")
spell = SpellChecker()
def rotate_word(word: str, n: int) -> str:
return word[n:] + word[:n]
def decrypt_pig_latin(sentence: str) -> str:
match = re.match(r'(.+?)(ay)([^ws]*)$', word)
if match:
base_word, _, punctuation = match.groups()
else:
base_word, punctuation = word, ""
for i in range(len(base_word)):
rotated = rotate_word(base_word, i)
if spell.known([rotated]):
valid_words.append(rotated + punctuation)
if valid_words:
decrypted_words.append('/'.join(valid_words))
else:
decrypted_words.append(word)
return ' '.join(decrypted_words)
input['Answer Expected'] = input['Encrypted Text'].apply(decrypt_pig_latin)
input.drop(columns='Encrypted Text', inplace=True)
print(input == test)
Solving the challenge of Decrypt Pig Latin Words with Python in Excel
Python in Excel solution 1 for Decrypt Pig Latin Words, proposed by Alejandro Campos:
Hello all.
import re
df = xl("A1:A10", headers=True)
def decrypt_pig_latin(encrypted_word):
pattern = re.compile(r'b(([aeiou]w*?)([^aeiou]?)|(w+))ayb')
def decrypt_match(match):
if match.group(2) is not None:
start = match.group(2)
end = match.group(3)
return f"{end}{start}"
elif match.group(4) is not None:
return match.group(4)
return match.group(0)
return decrypted_word
df['Decrypted Text'] = df['Encrypted Text'].apply(decrypt_pig_latin)
df.drop(columns=['Encrypted Text'], inplace=True)
df
Solving the challenge of Decrypt Pig Latin Words with R
R solution 1 for Decrypt Pig Latin Words, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
library(hunspell)
path = "Excel/511 Pig Latin Decrypter.xlsx"
input = read_excel(path, range = "A1:A10")
test = read_excel(path, range = "B1:B10")
len <- nchar(word)
if (n >= len) return(word)
substr(word, n + 1, len) %>% paste0(substr(word, 1, n))
}
decrypt_pig_latin <- function(sentence) {
punctuation <- str_extract(.x, "[[:punct:]]$")
len <- nchar(word)
if (length(valid_words) > 0) {
if (!is.na(punctuation)) {
return(paste0(concatenated_valid_words, punctuation))
} else {
return(concatenated_valid_words)
}
} else {
return(.x)
}
})
paste(decrypted_words, collapse = " ")
}
result = input %>%
mutate(`Answer Expected` = map_chr(`Encrypted Text`, decrypt_pig_latin)) %>%
select(`Answer Expected`)
result == test
&&&