Separate out the largest sequences of English alphabets and numbers from the given strings.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 361
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Extract Longest Alphanumeric Segment with Power Query
Power Query solution 1 for Extract Longest Alphanumeric Segment, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
MyFun = (Select, Remove, Field) =>
let
Delimiters = Text.ToList(Text.Remove(Field, Alphabets & Select)),
Split = Splitter.SplitTextByAnyDelimiter(Remove & Delimiters)(Field),
Calculate = List.Transform(Split, each [T = Text.Select(_, Select), L = Text.Length(T)]),
Max = List.Max(Calculate, 0, each [L])[L],
Filter = List.Select(Calculate, each [L] = Max and [T] <> ""),
Return = Text.Combine(List.Transform(Filter, each [T]), ", ")
in
Return,
Alphabets = {"A" .. "Z", "a" .. "z"},
Digits = {"0" .. "9"},
Record = Table.AddColumn(
Source,
"R",
each [
String = [String],
Alphabet = MyFun(Alphabets, Digits, [String]),
Digit = MyFun(Digits, Alphabets, [String])
]
),
Return = Table.FromRecords(Record[R])
in
ReturnPower Query solution 2 for Extract Longest Alphanumeric Segment, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
excluir = Text.Combine(
List.Distinct(
List.Select(
List.TransformMany(
Fonte[String],
each Text.ToList(_),
(x, y) => Text.Remove(y, {"0" .. "9", "a" .. "z", " ", "A" .. "Z"})
),
each _ <> ""
)
)
),
tab = Table.AddColumn(
Fonte,
"Personalizar",
each [
a = List.Transform(
Text.SplitAny([String], "0123456789" & excluir & " , "),
each {_} & {Text.Length(_)}
),
Alph = Text.Replace(
Text.Combine(
List.Transform(
List.Select(a, each _{1} = List.Max(List.Transform(a, each _{1}))),
each _{0}
),
","
),
",,,",
""
),
b = List.Transform(
Text.SplitAny(
[String],
"abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ" & excluir & " , "
),
each {_} & {Text.Length(_)}
),
Numb = Text.Replace(
Text.Replace(
Text.Combine(
List.Transform(
List.Select(b, each _{1} = List.Max(List.Transform(b, each _{1}))),
each _{0}
),
","
),
",,,",
""
),
",,",
""
)
][[Alph], [Numb]]
),
res = Table.ExpandRecordColumn(tab, "Personalizar", {"Alph", "Numb"})
in
resPower Query solution 3 for Extract Longest Alphanumeric Segment, proposed by An Nguyen:
let
CharList = List.Transform({1 .. 255}, (_) => Character.FromNumber(_)),
Less_Zero = List.Select(CharList, each _ < "0" or (_ > "9" and _ < "A") or (_ > "Z" and _ < "a")),
More_Nine = List.Select(CharList, each _ > "9"),
f = (txt, lst) =>
let
Delimiters = List.Combine({Less_Zero, lst}),
ListAfterSplit = List.RemoveItems(Splitter.SplitTextByAnyDelimiter(Delimiters)(txt), {""}),
MaxLength = List.Max(List.Transform(ListAfterSplit, each Text.Length(_))),
GetElements = List.Select(ListAfterSplit, each Text.Length(_) = MaxLength),
TextJoined = Text.Combine(GetElements, ", ")
in
TextJoined,
Dataset = Excel.CurrentWorkbook(){[Name = "raw"]}[Content],
Strings = List.Buffer(Dataset[String]),
GetAlpha = List.Transform(Strings, each f(_, {"0" .. "9"})),
GetNumeric = List.Transform(Strings, each f(_, More_Nine)),
Result = Table.FromColumns({GetAlpha, GetNumeric}, {"Alphabets", "Numbers"})
in
ResultPower Query solution 4 for Extract Longest Alphanumeric Segment, proposed by Mihai Radu O:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
special = Text.Combine(
List.Distinct(
Text.ToList(
Text.Combine(
Text.SplitAny(
Text.Combine(Source[String]),
Text.Combine({"A" .. "Z"} & {"a" .. "z"} & {"0" .. "9"})
)
)
)
)
),
del_nr = "0123456789",
del_txt = Text.Combine({"A" .. "Z"} & {"a" .. "z"}),
f = (col, del) =>
let
a = Text.SplitAny(col, special & del),
b = Text.Combine(
List.Select(
a,
each (Text.Length(_) = List.Max(List.Transform(a, each Text.Length(_)))) and _ <> ""
),
", "
)
in
b,
abc = Table.AddColumn(Source, "Alphabets", each f([String], del_nr)),
txt = Table.AddColumn(abc, "Numbers", each f([String], del_txt))[[Alphabets], [Numbers]]
in
txtPower Query solution 5 for Extract Longest Alphanumeric Segment, proposed by Glyn Willis:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(
Source,
{{"String", type text}, {"Alphabets", type text}, {"Numbers", type any}}
),
#"Added Custom" = Table.AddColumn(
#"Changed Type",
"Custom",
each [
string = Text.ToList([String]),
replace = List.Transform(
List.ReplaceMatchingItems(
string,
List.Transform({"A" .. "Z"} & {"a" .. "z"}, each {_} & {"x"})
& List.Transform({"0" .. "9"}, each {_} & {"y"})
),
each if not List.Contains({"x", "y"}, _) then "-" else _
),
group = Table.Group(
Table.FromColumns({string, replace}, {"T", "P"}),
{"P"},
{{"D", each _}, {"C", each Table.RowCount(_)}},
GroupKind.Local
),
T =
let
t = Table.SelectRows(group, each [P] = "x")
in
Text.Combine(
List.Transform(
Table.SelectRows(t, each [C] = List.Max(t[C]))[D],
each Text.Combine(_[T])
),
", "
),
A =
let
t = Table.SelectRows(group, each [P] = "y")
in
Text.Combine(
List.Transform(
Table.SelectRows(t, each [C] = List.Max(t[C]))[D],
each Text.Combine(_[T])
),
", "
),
result = [Text = T, Aplpha = A]
][result]
),
#"Expanded Custom" = Table.ExpandRecordColumn(
#"Added Custom",
"Custom",
{"Text", "Aplpha"},
{"Text", "Aplpha"}
)
in
#"Expanded Custom"Solving the challenge of Extract Longest Alphanumeric Segment with Excel
Excel solution 1 for Extract Longest Alphanumeric Segment, proposed by Bo Rydobon 🇹🇭:
=LET(m,
CHAR(
SEQUENCE(
255
)
),
f,
LAMBDA(
d,
MAP(
A2:A10,
LAMBDA(
a,
LET(
t,
TEXTSPLIT(
a,
d
),
TEXTJOIN(
", ",
,
REPT(
t,
LEN(
t
)=MAX(
LEN(
t
)
)
)
)
)
)
)
),
HSTACK(f(
FILTER(
m,
m<"a"
)
),
f(FILTER(m,
(m>"9")+(m<"0")))))Excel solution 2 for Extract Longest Alphanumeric Segment, proposed by Julian Poeltl:
=HSTACK(LET(STS,
A2:A10,
HSTACK(LET(R,
MAP(STS,
LAMBDA(ST,
LET(S,
SEQUENCE(
255
),
CWL,
CHAR(VSTACK(FILTER(
S,
S<65
),
FILTER(
S,
S>122
),
FILTER(S,
(S>90)*(S<97)))),
SL,
TEXTSPLIT(
ST,
CWL
),
LL,
LEN(
SL
),
ML,
MAX(
LL
),
TEXTJOIN(
", ",
,
FILTER(
TRANSPOSE(
SL
),
TRANSPOSE(
LL=ML
)
)
)))),
R),
LET(
R,
MAP(
STS,
LAMBDA(
ST,
LET(
S,
SEQUENCE(
255
),
CWN,
CHAR(
VSTACK(
FILTER(
S,
S<48
),
FILTER(
S,
S>57
)
)
),
SN,
TEXTSPLIT(
ST,
CWN
),
LN,
LEN(
SN
),
MN,
MAX(
LN
),
TEXTJOIN(
", ",
,
FILTER(
TRANSPOSE(
SN
),
TRANSPOSE(
LN=MN
)
)
)
)
)
),
IFERROR(
R*1,
R
)
))))Excel solution 3 for Extract Longest Alphanumeric Segment, proposed by Timothée BLIOT:
=REDUCE(
{"Alphabets",
"Numbers"},
A2:A10,
LAMBDA(
w,
v,
LET(
A,
REGEXEXTRACT(
v,
"([a-zA-Z]+)|([0-9]+)",
1
),
B,
LAMBDA(
m,
FILTER(
A,
m,
""
)
),
D,
B(
REGEXTEST(
A,
"d"
)
),
E,
B(
REGEXTEST(
A,
"D"
)
),
F,
LAMBDA(
n,
IFNA(
ARRAYTOTEXT(
FILTER(
n,
LEN(
n
)=MAX(
LEN(
n
)
)
)
),
""
)
),
VSTACK(
w,
HSTACK(
F(
E
),
F(
D
)
)
)
)
)
)Excel solution 4 for Extract Longest Alphanumeric Segment, proposed by Nikola Z Grujicic - Nikola Ž Grujičić:
=HSTACK(MAP(A2:A10,LAMBDA(a, LET(e, MID(a, SEQUENCE(LEN(a)),1),f, CODE(e), g, IF(1=((f>64)*(f<91)+(f>96)*(f<123)),e,"|"),h, TEXTSPLIT(TEXTJOIN("",,g),,"|",TRUE),i, FILTER(h, LEN(h)=MAX(LEN(h))),j, TEXTJOIN(", ",,i), o, IFERROR(j,""), o))), MAP(A2:A10,LAMBDA(a, LET(e, MID(a, SEQUENCE(LEN(a)),1),f, CODE(e),k, IF(1=(f>47)*(f<58),e,"|"),l, TEXTSPLIT(TEXTJOIN("",,k),,"|",TRUE),m, FILTER(l, LEN(l)=MAX(LEN(l))),n, TEXTJOIN(", ",,m), p, IFERROR(n, ""), IF(ISERR(1*p),p,1*p)))))Excel solution 5 for Extract Longest Alphanumeric Segment, proposed by Hussein SATOUR:
=TEXTSPLIT(
CONCAT(
MAP(
A2:A10,
LAMBDA(
x,
LET(
a,
TEXTSPLIT(
x,
,
CHAR(
VSTACK(
SEQUENCE(
64
),
SEQUENCE(
6,
,
91
)
)
)
),
b,
TEXTSPLIT(
LOWER(
x
),
,
CHAR(
VSTACK(
SEQUENCE(
47
),
SEQUENCE(
26,
,
97
)
)
)
),
c,
LEN(
a
),
d,
LEN(
b
),
TEXTJOIN(
",",
,
IF(
SUM(
c
)=0,
" ",
FILTER(
a,
c = MAX(
c
)
)
)
) &"|"& TEXTJOIN(
",",
,
FILTER(
b,
d = MAX(
d
)
)
)&"/"
)
)
)
),
"|",
"/",
1,
,
""
)Excel solution 6 for Extract Longest Alphanumeric Segment, proposed by Abdallah Ally:
=DROP(
IFNA(
REDUCE(
"",
A2:A10,
LAMBDA(
u,
v,
VSTACK(
u,
LET(
a,
v,
b,
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
c,
TRIM(
REDUCE(
"",
b,
LAMBDA(
x,
y,
IF(
OR(
CODE(
LOWER(
y
)
)=SEQUENCE(
26,
,
97
)
)+OR(
CODE(
LOWER(
y
)
)=SEQUENCE(
10,
,
48
)
& ),
x&y,
x&" "
)
)
)
),
f,
LAMBDA(
a,
b,
LET(
t,
TEXTSPLIT(
REDUCE(
"",
MID(
c,
SEQUENCE(
LEN(
c
)
),
1
),
LAMBDA(
x,
y,
IF(
OR(
CODE(
LOWER(
y
)
)=SEQUENCE(
a,
,
b
)
),
x&y,
x&" "
)
)
),
" "
),
e,
ARRAYTOTEXT(
FILTER(
t,
LEN(
t
)=MAX(
LEN(
t
)
),
""
)
),
IF(
TRIM(
SUBSTITUTE(
e,
",",
""
)
)="",
"",
e
)
)
),
IFERROR(
HSTACK(
f(
26,
97
),
f(
10,
48
)
),
""
)
)
)
)
),
""
),
1
)Excel solution 7 for Extract Longest Alphanumeric Segment, proposed by 🇵🇪 Ned Navarrete C.:
=DROP(
REDUCE(
"",
A2:A10,
LAMBDA(
c,
f,
LET(
a,
MID(
f,
SEQUENCE(
LEN(
f
)
),
1
),
m,
IFERROR(
a*1,
a
),
l,
TEXTSPLIT(
TEXTJOIN(
,
,
IF(
m>="a",
m,
"-"
)
),
,
"-"
),
n,
TEXTJOIN(
", ",
,
FILTER(
l,
LEN(
l
)=MAX(
LEN(
l
)
)
)
),
x,
TEXTSPLIT(
TEXTJOIN(
,
,
IF(
m<=9,
m,
"-"
)
),
,
"-"
),
y,
TEXTJOIN(
", ",
,
FILTER(
x,
LEN(
x
)=MAX(
LEN(
x
)
)
)
),
VSTACK(
c,
HSTACK(
n,
y
)
)
)
)
),
1
)Excel solution 8 for Extract Longest Alphanumeric Segment, proposed by Thang Van:
=0,
array,
""),
_aTOz,
TEXTJOIN(
"",
,
CHAR(
SEQUENCE(
91-65,
,
65,
1
)
)
),
_a,
MAP(
_temp,
LAMBDA(
_each,
IF(
ISNUMBER(
SEARCH(
_each,
_aTOz
)
),
_each,
""
)
)
),
temp,
CONCAT(
IF(
_a<>"",
_a,
";"
)
),
_max,
MAX(
LEN(
TEXTSPLIT(
temp,
,
";"
)
)
),
r,
MAP(
TEXTSPLIT(
temp,
,
";"
),
LAMBDA(
_each,
IF(
LEN(
_each
)=_max,
_each,
""
)
)
),
ARRAYTOTEXT(
FILTER(
r,
r<>""
)
)
))),
""),
_array2,
IFERROR(
MAP(
A2:A10,
LAMBDA(
_each1,
LET(
array,
MID(
_each1,
SEQUENCE(
LEN(
_each1
)
),
1
),
c,
--ISNUMBER(
array+0
),
d,
CONCAT(
IF(
c,
array,
";"
)
),
_max,
MAX(
LEN(
TEXTSPLIT(
d,
";",
,
)
)
),
e,
MAP(
TEXTSPLIT(
d,
";",
,
),
LAMBDA(
_each,
IF(
LEN(
_each
)=_max,
_each,
""
)
)
),
ARRAYTOTEXT(
FILTER(
e,
e<>""
)
)
)
)
),
""
),
VSTACK(
{"Alphabets",
"Numbers"},
HSTACK(
_array1,
_array2
)
))Excel solution 9 for Extract Longest Alphanumeric Segment, proposed by Charles Roldan:
=LET(
_TEXTTOARRAY,
LAMBDA(
String,
MID(
String,
SEQUENCE(
LEN(
String
)
),
1
)
),
_SPLIT,
LAMBDA(
String,
Codex,
LET(
Array,
_TEXTTOARRAY(
String
),
TEXTSPLIT(
CONCAT(
IF(
MOD(
MATCH(
CODE(
Array
),
Codex
),
2
),
"|",
Array
)
),
"|",
,
TRUE
)
)
),
_LONGEST,
LAMBDA(
Array,
ARRAYTOTEXT(
FILTER(
Array,
LEN(
Array
)=MAX(
LEN(
Array
)
)
)
)
),
f,
LAMBDA(
Codex,
MAP(
A2:A10,
LAMBDA(
String,
IFERROR(
_LONGEST(
_SPLIT(
String,
Codex
)
),
""
)
)
)
),
HSTACK(
f(
{1;65;91;97;123}
),
f(
{1;48;58}
)
)
)Excel solution 10 for Extract Longest Alphanumeric Segment, proposed by Bilal Mahmoud kh.:
=TEXTJOIN(
",",
TRUE,
LET(
e,
LET(
c,
MID(
A1,
SEQUENCE(
LEN(
A1
)
),
1
),
d,
MAP(
CODE(
c
),
LAMBDA(
x,
OR(
AND(
x>=65,
x <= 90
),
AND(
x>=97,
x<=122
)
)
)
),
LET(
a,
TEXTSPLIT(
CONCAT(
IF(
d,
c,
","
)
),
","
),
b,
LEN(
a
),
IF(
b=MAX(
b
),
a
)
)
),
FILTER(
e,
ISTEXT(
e
)
)
)
)
and for the numbers:
=TEXTJOIN(
",",
TRUE,
LET(
e,
LET(
c,
MID(
A1,
SEQUENCE(
LEN(
A1
)
),
1
),
d,
ISNUMBER(
c*1
),
LET(
a,
TEXTSPLIT(
CONCAT(
IF(
d,
c,
","
)
),
","
),
b,
LEN(
a
),
IF(
b=MAX(
b
),
a
)
)
),
FILTER(
e,
ISTEXT(
e
)
)
)
)Excel solution 11 for Extract Longest Alphanumeric Segment, proposed by Ziad A.:
=MAP(
A2:A10,
LAMBDA(
a,
MAP(
{"[A-Za-z]",
"d"},
LAMBDA(
e,
LET(
s,
SPLIT(
REGEXREPLACE(
a,
"("&e&"+)|.",
"$1 "
),
" "
),
JOIN(
", ",
FILTER(
s,
LEN(
s
)=MAX(
LEN(
s
)
)
)
)
)
)
)
)
)Excel solution 12 for Extract Longest Alphanumeric Segment, proposed by Giorgi Goderdzishvili:
=LET(k,
MAP(A2:A10,
LAMBDA(t,
LET(
_str,
t,
_cr,
MID(
_str,
SEQUENCE(
,
LEN(
_str
)
),
1
),
_cd,
CODE(
LOWER(
_cr
)
),
_tx,
-- (_cd>96)*(_cd<123),
_sc,
SCAN(
0,
_tx,
LAMBDA(
x,
y,
IF(
y=0,
0,
x+y
)
)
),
_mx,
MAX(
_sc
),
_ocr,
FILTER(
SEQUENCE(
,
COLUMNS(
_sc
)
),
_sc=_mx
),
_mp,
MAP(
_ocr,
LAMBDA(
x,
MID(
_str,
x - _mx+1,
_mx
)
)
),
_nm,
-- (_cd>47)*(_cd<58),
_sc2,
SCAN(
0,
_nm,
LAMBDA(
x,
y,
IF(
y=0,
0,
x+y
)
)
),
_mx2,
MAX(
_sc2
),
_ocr2,
FILTER(
SEQUENCE(
,
COLUMNS(
_sc2
)
),
_sc2=_mx2
),
_mp2,
MAP(
_ocr2,
LAMBDA(
x,
MID(
_str,
x - _mx2+1,
_mx2
)
)
),
_Hs,
TEXTJOIN(
", ",
TRUE,
_mp
)&"-"&TEXTJOIN(
", ",
TRUE,
_mp2
),
_Hs))),
HSTACK(
TEXTBEFORE(
k,
"-"
),
TEXTAFTER(
k,
"-"
)
))Excel solution 13 for Extract Longest Alphanumeric Segment, proposed by Abdelrahman Omer, MBA, PMP:
=LET(k,
MAP(A2:A10,
LAMBDA(s,
LET(a,
MID(
s,
SEQUENCE(
LEN(
s
)
),
1
),
b,
CONCAT(IF(N((a>="a")),
a,
"/")),
c,
CONCAT(IF((a<"a")*(a>="0"),
a,
"/")),
d,
TEXTSPLIT(
b,
,
"/"
),
e,
TEXTSPLIT(
c,
,
"/"
),
IF(
MAX(
LEN(
d
)
)=0,
"",
ARRAYTOTEXT(
FILTER(
d,
LEN(
d
)=MAX(
LEN(
d
)
)
)
)
)&"-"&IF(
MAX(
LEN(
e
)
)=0,
"",
ARRAYTOTEXT(
FILTER(
e,
LEN(
e
)=MAX(
LEN(
e
)
)
)
)
)))),
HSTACK(
TEXTBEFORE(
k,
"-"
),
TEXTAFTER(
k,
"-"
)
))_x000D_
Excel solution 14 for Extract Longest Alphanumeric Segment, proposed by Jeremy Freelove:
1) TEXTSPLIT function allows multiple delimiters at once via array.
_x000D_
Solving the challenge of Extract Longest Alphanumeric Segment with Python
_x000D_
Python solution 1 for Extract Longest Alphanumeric Segment, proposed by Jan Willem Van Holst:
Python
import pandas as pd
import re
df = pd.read_csv(r"C:UsersvanhoDownloadschallange136.csv", sep=";")
mylist=df['String'].tolist()
def fx(inputstring):
cleanString = re.sub('[^A-Za-z0-9 ]+', ' ', inputstring)
splitList = re.split(r'(d+|s)s*', cleanString)
numberList = [ elem for elem in splitList if elem.isnumeric()]
alphaList = [ elem for elem in splitList if elem.isalpha()]
if numberList == []:
resultNum = []
else:
maxNum = max([len(elem) for elem in numberList])
resultNum = [elem for elem in numberList if len(elem)==maxNum]
if alphaList == []:
resultAlpha = []
else:
maxAlpha = max([len(elem) for elem in alphaList])
resultAlpha = [elem for elem in alphaList if len(elem)==maxAlpha]
return resultAlpha, resultNum
print([fx(elem) for elem in mylist])
_x000D_
Solving the challenge of Extract Longest Alphanumeric Segment with Python in Excel
_x000D_
Python in Excel solution 1 for Extract Longest Alphanumeric Segment, proposed by Abdallah Ally:
import pandas as pd
import re
file_path = 'Excel_Challenge_361 - Longest Sequence of Alphabets and Numbers.xlsx'
df = pd.read_excel(file_path)
df.replace(float('nan'), '', inplace=True)
df = df.astype(str)
# Perform data transformation and cleansing
def longest_sequence_num_alph(col):
chars = re.findall(r'([a-zA-Z]*)', col)
nums= re.findall(r'(d*)', col)
alphabets = ', '.join([x for x in chars if len(x) == max([len(y) for y in chars]) and x != ''])
numbers = ', '.join([x for x in nums if len(x) == max([len(y) for y in nums]) and x != ''])
return alphabets, numbers
df[['My Alphabets', 'My Numbers']] = df['String'].apply(longest_sequence_num_alph).tolist()
df['Correct'] = df.apply(
lambda x: (x['Alphabets'] == x['My Alphabets']) & (x['Numbers'] == x['My Numbers']), axis=1)
df
_x000D_
&