Extract the words which are either numbers followed by all uppercase English letters or vice versa. There may be special characters in the words. The output need to be shown in uppercase English letters_numbers without any special character. Ex. 676ABC, PQ88, 456@RT, UK_457,8@78*U/T Not valid – 456@Rt (t is small case), GHI 45 (GHI and 45 both are separate words not one word), 787UI56 (rule is number followed by uppercase English letters or vice versa which is 787UI. But it is once again followed by a number which makes it invalid. If you take vice versa, then it is UI56 but 787 makes it invalid)
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 191
Challenge Difficulty: ⭐️⭐️⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Extract Valid Structured Words with Power Query
Power Query solution 1 for Extract Valid Structured Words, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
A = {"A" .. "Z"},
a = {"a" .. "z"},
N = {"0" .. "9"},
S = Table.TransformRows(
Source,
each Text.Combine(
List.TransformMany(
List.Select(
Text.Split([Text], " "),
(w) =>
let
c = each List.Intersect({_, Text.ToList(w)}) <> {}
in
List.AllTrue({c(A), not c(a), c(N)})
),
(i) =>
let
w = Text.Select(i, A & N),
e = each List.RemoveItems(Text.SplitAny(w, Text.Combine(_)), {""})
in
{{}, {Text.Combine(e(N) & {"_"} & e(A))}}{Number.From(List.Count(e(A) & e(N)) = 2)},
(i, _) => _
),
", "
)
)
in
SPower Query solution 2 for Extract Valid Structured Words, proposed by Kris Jaganah:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Ans = Table.AddColumn(
Source,
"Answer Expected",
each
let
a = Text.SplitAny([Text], Text.Combine({"a" .. "z", " "})),
B = List.Transform,
c = List.RemoveNulls(
B(
{1 .. 255},
each Character.FromNumber(
if _ > 47 and _ < 58 then null else if _ > 64 and _ < 91 then null else _
)
)
),
d = B(a, each if Text.Select(_, c) <> "" then _ else null),
e = B(
d,
each
let
x = Text.Remove(_, c)
in
if x = "" then null else x
),
f = Text.Combine(
B(
e,
each
let
x = Text.Select(_, {"A" .. "Z"}) & "_" & Text.Select(_, {"0" .. "9"})
in
if Text.Start(x, 1) = "_" then null else x
),
", "
)
in
f
)
in
AnsPower Query solution 3 for Extract Valid Structured Words, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Digits = {"0" .. "9"},
Alphabets = {"A" .. "Z"},
Return = Table.AddColumn(
Source,
"Answer",
each [
T = Text.Select([Text], {"a" .. "z", " "} & Digits & Alphabets),
S1 = Text.Split(T, " "),
S2 = List.Transform(
S1,
(f) =>
[
s1 = Splitter.SplitTextByCharacterTransition(Alphabets, Digits)(f),
s2 = List.Transform(
s1,
(x) => Splitter.SplitTextByCharacterTransition(Digits, Alphabets)(x)
),
c = List.Combine(s2),
r = List.Sort(c, 1)
][r]
),
F = List.TransformMany(
S2,
(x) => {List.Count(x)},
(x, y) => if y = 2 then x{0} & "_" & x{1} else null
),
R = Text.Combine(F, ", ")
][R]
)
in
ReturnPower Query solution 4 for Extract Valid Structured Words, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.AddColumn(
Source,
"Answer",
each
let
a = Text.Split([Text], " "),
b = k(a, each j(l(_), {"A" .. "Z"})),
c = List.Distinct(l(n(m(Source[Text]), {"A" .. "Z", "a" .. "z", " ", "0" .. "9"}))),
d = k(b, each j(l(_), c)),
e = k(d, each not j(l(_), {"a" .. "z"})),
f = i(e, each n(_, c)),
g = i(
f,
each m(
i(
h({"A" .. "Z"}, {"0" .. "9"})(_),
(x) => m(List.Reverse(List.Sort(h({"0" .. "9"}, {"A" .. "Z"})(x))), "_")
),
"_"
)
),
h = Splitter.SplitTextByCharacterTransition,
i = List.Transform,
j = List.ContainsAny,
k = List.Select,
l = Text.ToList,
m = Text.Combine,
n = Text.Remove
in
m(g, ", ")
)
in
SolPower Query solution 5 for Extract Valid Structured Words, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
res = Table.TransformColumns(
Fonte,
{
"Text",
each
let
a = List.Select(
Text.Split(_, " "),
(y) =>
Text.Length(Text.Select(y, {"A" .. "Z"}))
> 0 and Text.Length(Text.Select(y, {"0" .. "9"}))
> 0 and Text.Length(Text.Select(y, {" " .. "/", "[" .. "_"}))
> 0 and Text.Length(Text.Select(y, {"a" .. "z"})) = 0
),
b = Text.Combine(
List.Transform(
a,
each Text.Select(_, {"A" .. "Z"}) & "_" & Text.Select(_, {"0" .. "9"})
),
", "
)
in
b
}
)
in
resPower Query solution 6 for Extract Valid Structured Words, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
A = Table.AddColumn(
Source,
"T1",
each Text.Select([Text], {"0" .. "9", "a" .. "z", "A" .. "Z", " "})
),
B = Table.ExpandListColumn(
Table.TransformColumns(
A,
{
{
"T1",
Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv),
let
itemType = (type nullable text) meta [Serialized.Text = true]
in
type {itemType}
}
}
),
"T1"
),
C = Table.TransformColumnTypes(B, {{"T1", type text}}),
D = Table.AddColumn(
C,
"Te",
each
if Text.Length(Text.Remove([T1], {"0" .. "9"})) = Text.Length([T1]) then
null
else if Text.Length(Text.Remove([T1], {"0" .. "9"})) = 0 then
null
else if Text.Remove([T1], {"0" .. "9"})
& Text.Remove([T1], {"A" .. "Z"}) = [T1] or Text.Remove([T1], {"A" .. "Z"})
& Text.Remove([T1], {"0" .. "9"}) = [T1]
then
Text.Remove([T1], {"0" .. "9"}) & "_" & Text.Remove([T1], {"A" .. "Z"})
else
null
),
E = Table.SelectRows(D, each ([Te] <> null)),
F = Table.Group(E, {"Text"}, {{"A", each Text.Combine([Te], ","), type text}}),
G = Table.NestedJoin(Source, {"Text"}, F, {"Text"}, "N"),
H = Table.ExpandTableColumn(G, "N", {"A"}, {"N.A"}),
Sol = Table.SelectColumns(H, {"N.A"})
in
SolPower Query solution 7 for Extract Valid Structured Words, proposed by Yaroslav Drohomyretskyi:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Words = Table.SelectRows(
Table.AddColumn(
Table.ExpandListColumn(
Table.AddColumn(Source, "Words", each Text.Split([Text], " ")),
"Words"
),
"Format",
each
if [Words]
= Text.Upper([Words]) and Text.Select([Words], {"0" .. "9"})
<> "" and Text.Select([Words], {"A" .. "Z"})
<> ""
then
Text.Select([Words], {"A" .. "Z"}) & "_" & Text.Select([Words], {"0" .. "9"})
else
null
),
each ([Format] <> null)
),
CheckTransitions = Table.AddColumn(
Words,
"CheckTransitions",
each
if List.Count(
Splitter.SplitTextByCharacterTransition({"0" .. "9"}, {"A" .. "Z"})(
Text.Select([Words], {"A" .. "Z", "0" .. "9"})
)
)
< 3
and List.Count(
Splitter.SplitTextByCharacterTransition({"A" .. "Z"}, {"0" .. "9"})(
Text.Select([Words], {"A" .. "Z", "0" .. "9"})
)
)
< 3
then
[Format]
else
null
),
Group = Table.Group(
CheckTransitions,
{"Text"},
{{"Answer", each Text.Combine(_[CheckTransitions], ", ")}}
)
in
GroupPower Query solution 8 for Extract Valid Structured Words, proposed by Ahmed Ariem:
let
source= Excel.CurrentWorkbook(){[Name="tbl"]}[Content],
PromoteHeader = Table.PromoteHeaders(source, [PromoteAllScalars=true]),
AddColumn= Table.AddColumn(PromoteHeader, "spl", each [
f1 = List.Buffer(List.Transform({65..90},Character.FromNumber)),
f2 = List.Buffer(List.Transform({97..122},Character.FromNumber)),
f3 =List.Buffer({ "0".."9"}),
f4 =List.Buffer(List.Transform({33..47}&{58..64}&{91..96}&{123..126},Character.FromNumber)),
txt1= List.Select (List.Transform( Text.Split([Text]," "), (x)=>if Text.Select(x,f1)<>"" and Text.Select(x,f3) <>"" and Text.Select(x,f2) =""then x else null),(x)=>x<> null) ,
txt2 = List.Transform(txt1,(x)=> Text.Remove(x,f4)),
txt3 = List.Transform(txt2,(x)=>
if Text.Select(Text.Trim(x,f3),f3)="" then x else null
),
txt4 = List.Transform(txt3, (x)=> Text.Select(x,f1)&"_"&Text.Select(x,f3)),
txt5 = Text.Combine(txt4,", ")
]
[txt5])
in
Power Query solution 9 for Extract Valid Structured Words, proposed by Challa Sai Kumar Reddy:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Answer = Table.AddColumn(
Source,
"Answer",
each
let
a = Text.Split([Text], " "),
b = List.Select(a, each List.Contains({"A" .. "Z"}, Text.Start(_, 1))),
c = List.Distinct(List.Combine(List.Transform(Source[Text], each Text.ToList(_)))),
d = List.Select(b, each List.Contains(c, Text.Start(_, 1))),
e = List.Select(d, each not List.Contains({"a" .. "z"}, Text.Start(_, 1))),
f = List.Transform(e, each Text.Remove(_, c)),
g = List.Transform(f, each Text.Combine(List.Sort(List.Reverse(Text.ToList(_))), "")),
h = Text.Splitter.SplitTextByCharacterTransition,
i = List.Transform,
j = List.ContainsAny,
k = List.Select,
l = Text.ToList,
m = Text.Combine,
n = Text.Remove
in
m(g, ", ")
)
in
AnswerSolving the challenge of Extract Valid Structured Words with Excel
Excel solution 1 for Extract Valid Structured Words, proposed by Bo Rydobon 🇹🇭:
=MAP(
A2:A11,
LAMBDA(
a,
TEXTJOIN(
", ",
,
MAP(
TEXTSPLIT(
IFNA(
REGEXREPLACE(
a,
"[^w ]|_",
),
a
),
" "
),
LAMBDA(
b,
LET(
c,
IFNA(
REGEXEXTRACT(
b,
"[A-Z]+"
)&"_"®EXEXTRACT(
b,
"d+"
),
),
REPT(
c,
LEN(
c
)-1=LEN(
b
)
)
)
)
)
)
)
)Excel solution 2 for Extract Valid Structured Words, proposed by Bo Rydobon 🇹🇭:
=MAP(
A2:A11,
LAMBDA(
a,
TEXTJOIN(
", ",
,
MAP(
TEXTSPLIT(
a,
" "
),
LAMBDA(
b,
LET(
m,
MID(
b,
SEQUENCE(
LEN(
b
)
),
1
),
c,
m>"9",
n,
1-ISERR(
-m
),
REPT(
CONCAT(
REPT(
m,
c
),
"_",
REPT(
m,
n
)
),
EXACT(
b,
UPPER(
b
)
)*OR(
m<"0"
)*OR(
c
)*OR(
n
)
)
)
)
)
)
)
)Excel solution 3 for Extract Valid Structured Words, proposed by Bo Rydobon 🇹🇭:
=MAP(
A2:A11,
LAMBDA(
a,
TEXTJOIN(
", ",
,
IFNA(
MAP(
REGEXEXTRACT(
a,
"b[^a-zs]+b",
1
),
LAMBDA(
b,
REPT(
CONCAT(
REGEXEXTRACT(
b,
"[A-Z]",
1
),
"_",
REGEXEXTRACT(
b,
"d",
1
)
),
REGEXTEST(
b,
"[^w]|_"
)
)
)
),
""
)
)
)
)Excel solution 4 for Extract Valid Structured Words, proposed by محمد حلمي:
=MAP(
A2:A11,
LAMBDA(
a,
TEXTJOIN(
", ",
,
MAP(
TEXTSPLIT(
a,
" "
),
LAMBDA(
a,
LET(
i,
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
j,
ISNUMBER(
-i
),
REPT(
CONCAT(
REPT(
i,
i>"9"
),
"_",
&REPT(
i,
j
)
),
EXACT(
a,
UPPER(
a
)
)* OR(
i<"0"
)*OR(
j
)
)
)
)
)
)
)
)Excel solution 5 for Extract Valid Structured Words, proposed by Julian Poeltl:
=MAP(
A2:A11,
LAMBDA(
T,
LET(
SP,
TEXTSPLIT(
T,
{" ",
", "}
),
TEXTJOIN(
", ",
,
IFERROR(
MAP(
SP,
LAMBDA(
A,
LET(
SP,
MID(
A,
SEQUENCE(
LEN(
A
)
),
1
),
C,
ISNUMBER(
MAP(
SP,
LAMBDA(
A,
XMATCH(
TRUE,
EXACT(
A,
CHAR(
64+SEQUENCE(
26
)
)
),
0
)
)
)
),
NL,
ISNUMBER(
MAP(
SP,
LAMBDA(
A,
XMATCH(
TRUE,
EXACT(
A,
CHAR(
97+SEQUENCE(
26
)
)
),
0
)
)
)
),
N,
ISNUMBER(
SP*1
),
O,
MAP(
C,
N,
LAMBDA(
A,
B,
OR(
A,
B
)
)
),
F,
FILTER(
C,
O
),
IF(
AND(
SUM(
--DROP(
F<>DROP(
F,
1
),
-1
)
)=1,
SUM(
--C
),
SUM(
--N
),
SUM(
--NL
)=0
),
TEXTJOIN(
"_",
,
CONCAT(
FILTER(
SP,
C
)
),
CONCAT(
FILTER(
SP,
N
)
)
),
""
)
)
)
),
""
)
)
)
)
)Excel solution 6 for Extract Valid Structured Words, proposed by JvdV -:
=IFNA(
REGEXREPLACE(
REGEXREPLACE(
A2:A11,
"[^A-Za-zd ]+",
),
"b((d+)([A-Z]+)|((?3))(d+))b(?=(.+(?1)))?|.",
"${3:+$3_}${4:+$4_}$2$5${6:+, }"
),
""
)
Perfect usecase for replacement string conditionals. If you haven't allready read about them on my blog:
https://buymeacoffee.com/jvdv/regexreplace-replacement-string-conditionals
For those interested; In the inner REGEXREPLACE()Solving the challenge of Extract Valid Structured Words with Python in Excel
Python in Excel solution 1 for Extract Valid Structured Words, proposed by Abdallah Ally:
import pandas as pd
import re
def extract_words(text):
pattern1 = r'[^ A-Za-z0-9]'
pattern2 = r'b(d+)([A-Z]+)b|b([A-Z]+)(d+)b'
matches = re.findall(pattern2, re.sub(pattern1, '', text))
joined_matches = [('_'.join(x[:2][::-1]), '_'.join(x[2:])) for x in matches]
final_text = ', '.join([x for y in joined_matches for x in y if x != '_'])
return final_text
file_path = 'PQ_Challenge_191.xlsx'
df = pd.read_excel(file_path)
# Perform data wrangling
df = df.replace(float('nan'), '')
df['My Answer'] = df['Text'].map(extract_words)
df['Check'] = df['Answer Expected'] == df['My Answer']
df
&&