Extract the substrings which have longest sequence of capital (uppercase) letters in given words. Ex. LINkEDIn => LIN and EDI are longest sequences of uppercase letters.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 458
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Longest Uppercase Substring with Power Query
Power Query solution 1 for Longest Uppercase Substring, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Ans = Table.AddColumn(
Source,
"Ans",
each
let
b = Text.SplitAny([Words], Text.Combine({"a" .. "z"}))
in
Text.Combine(
List.Select(b, each Text.Length(_) = List.Max(List.Transform(b, Text.Length)) and _ > ""),
", "
)
)
in
AnsPower Query solution 2 for Longest Uppercase Substring, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.AddColumn(
Source,
"Answer",
each
let
a = Splitter.SplitTextByCharacterTransition({"a" .. "z"}, {"A" .. "Z"})([Words]),
b = List.Combine(
List.Transform(
a,
each Splitter.SplitTextByCharacterTransition({"A" .. "Z"}, {"a" .. "z"})(_)
)
),
c = List.Select(b, each _ = Text.Upper(_)),
d = List.Max(List.Transform(c, each Text.Length(_))),
e = Text.Combine(List.Select(c, each Text.Length(_) = d), ", ")
in
e
)
in
SolPower Query solution 3 for Longest Uppercase Substring, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.AddColumn(
Source,
"Answer",
each
let
a = List.Accumulate({"a" .. "z"}, [Words], (s, c) => Text.Replace(s, c, ".")),
b = List.Select(Text.Split(a, "."), each _ <> ""),
c = List.Max(List.Transform(b, each Text.Length(_))),
d = List.Select(b, each Text.Length(_) = c)
in
Text.Combine(d, ", ")
)
in
SolPower Query solution 4 for Longest Uppercase Substring, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
res = Table.AddColumn(
Fonte,
"Personalizar",
each [
lt = List.Transform,
a = lt(
Splitter.SplitTextByCharacterTransition({"A" .. "Z", "a" .. "z"}, {"a" .. "z"})([Words]),
(x) => Text.Select(x, {"A" .. "Z"})
),
b = lt(a, each {_} & {Text.Length(_)}),
c = Text.Combine(
List.Select(
lt(List.Select(b, (x) => x{1} = List.Max(lt(b, each _{1}))), (y) => y{0}),
each _ <> ""
),
", "
)
][c]
)
in
resPower Query solution 5 for Longest Uppercase Substring, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.AddColumn(
S,
"Expected Answer",
each
let
a = Splitter.SplitTextByCharacterTransition({"A" .. "Z"}, {"a" .. "z"})([Words]),
b = List.Transform(a, Splitter.SplitTextByCharacterTransition({"a" .. "z"}, {"A" .. "Z"})),
c = List.Transform(List.Combine(b), each Text.Select(_, {"A" .. "Z"})),
d = Table.FromColumns({List.Select(c, each _ <> "")}, {"T"}),
e = Table.AddColumn(d, "C", each Text.Length([T])),
f = Table.Group(e, {"C"}, {"G", each _}),
g = Table.Combine(Table.MaxN(f, "C", 1)[[G]][G])[T],
h = try Text.Combine(g, ", ") otherwise null
in
h
)
in
SolPower Query solution 6 for Longest Uppercase Substring, proposed by Venkata Rajesh:
let
Source = Data,
Output = Table.AddColumn(
Source,
"Expected",
each [
split = Splitter.SplitTextByCharacterTransition({"a" .. "z"}, {"A" .. "Z"})([Words]),
cap = List.Transform(split, each Text.Select(_, {"A" .. "Z"})),
max = List.Max(List.Transform(cap, each Text.Length(_))),
result = Text.Combine(List.Select(cap, each Text.Length(_) = max), ", ")
][result]
)
in
OutputSolving the challenge of Longest Uppercase Substring with Excel
Excel solution 1 for Longest Uppercase Substring, proposed by Bo Rydobon 🇹🇭:
=MAP(
A2:A11,
LAMBDA(
a,
LET(
b,
TEXTSPLIT(
a,
CHAR(
SEQUENCE(
26,
,
97
)
)
),
l,
LEN(
b
),
TEXTJOIN(
", ",
,
REPT(
b,
l=MAX(
l
)
)
)
)
)
)Excel solution 2 for Longest Uppercase Substring, proposed by John V.:
=MAP(
A2:A11,
LAMBDA(
x,
LET(
i,
TEXTSPLIT(
x,
CHAR(
ROW(
97:122
)
)
),
n,
LEN(
i
),
TEXTJOIN(
", ",
,
REPT(
i,
n=MAX(
n
)
)
)
)
)
)Excel solution 3 for Longest Uppercase Substring, proposed by محمد حلمي:
=MAP(
A2:A11,
LAMBDA(
a,
LET(
i,
TEXTSPLIT(
a,
CHAR(
SEQUENCE(
26
)+96
)
),
j,
LEN(
i
),
TEXTJOIN(
", ",
,
REPT(
i,
j=MAX(
j
)
)
)
)
)
)Excel solution 4 for Longest Uppercase Substring, proposed by Kris Jaganah:
=MAP(
A2:A11,
LAMBDA(
x,
LET(
a,
TEXTSPLIT(
x,
,
CHAR(
SEQUENCE(
26,
,
97
)
),
1
),
b,
LEN(
a
),
IFERROR(
ARRAYTOTEXT(
FILTER(
a,
MAX(
b
)=b
)
),
""
)
)
)
)Excel solution 5 for Longest Uppercase Substring, proposed by Julian Poeltl:
=MAP(
A2:A11,
LAMBDA(
W,
LET(
SP,
TEXTSPLIT(
W,
CHAR(
VSTACK(
SEQUENCE(
64
),
SEQUENCE(
159,
,
91
)
)
)
),
L,
LEN(
SP
),
M,
MAX(
L
),
TEXTJOIN(
", ",
,
FILTER(
SP,
L=M
)
)
)
)
)Excel solution 6 for Longest Uppercase Substring, proposed by Timothée BLIOT:
=MAP(
A2:A11,
LAMBDA(
z,
LET(
A,
REGEXEXTRACT(
z,
"[A-Z]+",
1
),
TEXTJOIN(
", ",
,
IFNA(
IF(
LEN(
A
)=MAX(
LEN(
A
)
),
A,
""
),
""
)
)
)
)
)Excel solution 7 for Longest Uppercase Substring, proposed by Duy Tùng:
=IFERROR(
MAP(
A2:A11,
LAMBDA(
x,
LET(
a,
TEXTSPLIT(
x,
,
CHAR(
SEQUENCE(
26,
,
97
)
),
1
),
b,
LEN(
a
),
ARRAYTOTEXT(
FILTER(
a,
b=MAX(
b
)
)
)
)
)
),
""
)Excel solution 8 for Longest Uppercase Substring, proposed by Sunny Baggu:
=MAP(
A2:A11,
LAMBDA(
x,
LET(
a,
TEXTSPLIT(
x,
,
CHAR(
SEQUENCE(
26,
,
CODE(
"a"
)
)
),
1
),
IFERROR(
ARRAYTOTEXT(
FILTER(
a,
LEN(
a
) = MAX(
LEN(
a
)
)
)
),
""
)
)
)
)Excel solution 9 for Longest Uppercase Substring, proposed by Sunny Baggu:
=MAP(
A2:A11,
LAMBDA(
t,
LET(
_a,
UNIQUE(
TEXTSPLIT(
t,
,
TEXTSPLIT(
t,
,
CHAR(
SEQUENCE(
26,
,
CODE(
"A"
)
)
),
1
)
)
),
_b,
LEN(
_a
) = MAX(
LEN(
_a
)
),
IFERROR(
ARRAYTOTEXT(
FILTER(
_a,
_b,
""
)
),
t
)
)
)
)Excel solution 10 for Longest Uppercase Substring, proposed by Abdallah Ally:
=MAP(
A2:A11,
LAMBDA(
x,
LET(
a,
x,
b,
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
c,
TEXTSPLIT(
REDUCE(
"",
b,
LAMBDA(
x,
y,
IF(
OR(
CODE(
y
)=SEQUENCE(
26,
,
65
)
),
x&y,
x&" "
)
)
),
" "
),
TEXTJOIN(
", ",
,
FILTER(
c,
LEN(
c
)=MAX(
LEN(
c
)
)
)
)
)
)
)Excel solution 11 for Longest Uppercase Substring, proposed by 🇵🇪 Ned Navarrete C.:
=MAX(
l
))),
""))))Excel solution 12 for Longest Uppercase Substring, proposed by Asheesh Pahwa:
=LET(
w,
A2:A11,
alp,
CHAR(
SEQUENCE(
26,
,
97
)
),
n,
IFNA(
DROP(
REDUCE(
"",
w,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
t,
TEXTSPLIT(
y,
alp
),
FILTER(
t,
t<>"",
""
)
)
)
)
),
1
),
""
),
BYROW(
n,
LAMBDA(
x,
LET(
l,
LEN(
x
),
m,
MAX(
l
),
e,
m=l,
TEXTJOIN(
",",
1,
FILTER(
x,
e
)
)
)
)
)
)Excel solution 13 for Longest Uppercase Substring, proposed by ferhat CK:
=MAX(
LE&N(
TEXTSPLIT(
x;
","
)
)
)))));
""))Excel solution 14 for Longest Uppercase Substring, proposed by Andy Heybruch:
=MAP(
A2:A11,
LAMBDA(
_word,
LET(
_x,
MID(
_word,
SEQUENCE(
LEN(
_word
)
),
1
),
_cap,
CONCAT(
IF(
ISNUMBER(
XMATCH(
CODE(
_x
),
SEQUENCE(
,
26,
65
)
)
),
_x,
"|"
)
),
_substring,
TEXTSPLIT(
_cap,
"|"
),
_L,
LEN(
_substring
),
IF(
MAX(
_L
)=0,
"",
ARRAYTOTEXT(
FILTER(
_substring,
_L=MAX(
_L
)
)
)
)
)
)
)Excel solution 15 for Longest Uppercase Substring, proposed by Mihai Radu O:
= MAX(
LEN(
c
)
)))
)
)
)Excel solution 16 for Longest Uppercase Substring, proposed by Ziad A.:
=LET(
s,
SPLIT(
REGEXREPLACE(
A2,
"([A-Z]+)|.",
"$1 "
),
" "
),
JOIN(
", ",
FILTER(
s,
LEN(
s
)=MAX(
LEN(
s
)
)
)
)
)Excel solution 17 for Longest Uppercase Substring, proposed by Rayan S.:
=MAP(
A2:A11,
LAMBDA(
x,
LET(
s,
TEXTSPLIT(
x,
CHAR(
SEQUENCE(
26,
,
97
)
)
),
ARRAYTOTEXT(
IF(
SUM(
LEN(
s
)
)=0,
"",
FILTER(
s,
LEN(
s
)=MAX(
LEN(
s
)
)
)
)
)
)
)
)Excel solution 18 for Longest Uppercase Substring, proposed by Ernesto Vega Castillo:
=BYROW(
A2:A11,
LAMBDA(
a,
LET(
b,
TEXTSPLIT(
a,
,
CHAR(
SEQUENCE(
26,
,
CODE(
"a"
)
)
),
1
),
c,
LEN(
b
),
d,
HSTACK(
b,
c
),
e,
FILTER(
TAKE(
d,
,
1
),
c=MAX(
c
)
),
IFERROR(
ARRAYTOTEXT(
e
),
""
)
)
)
)Excel solution 19 for Longest Uppercase Substring, proposed by Burhan Cesur:
=MAP(
A2:A11,
LAMBDA(
f,
LET(
a,
f,
b,
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
c,
LEN(
SCAN(
"",
b,
LAMBDA(
x,
y,
IF(
EXACT(
y,
UPPER(
y
)
),
x&y,
""
)
)
)
),
d,
SCAN(
"",
b,
LAMBDA(
x,
y,
IF(
EXACT(
y,
UPPER(
y
)
),
x&y,
""
)
)
),
TEXTJOIN(
", ",
,
FILTER(
d,
c=MAX(
c
)
)
)
)
)
)Excel solution 20 for Longest Uppercase Substring, proposed by Wanderlei Huttel:
=SEERRO(
UNIRTEXTO(
", ";;REGEXEXTRACT(
A2;"[A-Z]{3,}";1
)
);""
)
Porém no exemplo as respostas não tem um padrão igual.
In English
=IFERROR(
TEXTJOIN(
", ",
,
REGEXEXTRACT(
A2,
"[A-Z]{3,}",
1
)
),
""
)Solving the challenge of Longest Uppercase Substring with Python
Python solution 1 for Longest Uppercase Substring, proposed by Konrad Gryczan, PhD:
import pandas as pd
import re
import numpy as np
df = pd.read_excel("458 Maximum Consecutive Uppercase Alphabets.xlsx", usecols="A:B")
input = df["Words"].tolist()
test = df["Expected Answer"].tolist()
def get_longest_capital(string):
caps = re.findall("[A-Z]+", string)
caps_len = max([len(cap) for cap in caps]) if caps else None
caps = [cap for cap in caps if len(cap) == caps_len]
return ", ".join(caps)
output = [get_longest_capital(string) if get_longest_capital(string) else np.nan for string in input]
print(output == test) # True
Solving the challenge of Longest Uppercase Substring with Python in Excel
Python in Excel solution 1 for Longest Uppercase Substring, proposed by Abdallah Ally:
# I love regular expressions
import pandas as pd
import re
def consecutive_uppercase(text):
text = re.sub('[^A-Z]', ' ', text).split()
text = [x for x in text if len(x) == max([len(y) for y in text])]
return ', '.join(text)
file_path = 'Excel_Challenge_458 - Maximum Consecutive Uppercase Alphabets.xlsx'
df = pd.read_excel(file_path).replace(float('nan'), '')
# Perform data wrangling
df['My Answer'] = df['Words'].map(lambda x: consecutive_uppercase(x))
df['Check'] = df['Expected Answer'] == df['My Answer']
df
Solving the challenge of Longest Uppercase Substring with R
R solution 1 for Longest Uppercase Substring, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel("Excel/458 Maximum Consecutive Uppercase Alphabets.xlsx", range = "A1:A11")
test = read_excel("Excel/458 Maximum Consecutive Uppercase Alphabets.xlsx", range = "B1:B11")
get_longest_capital = function(string) {
caps = str_extract_all(string, "[A-Z]+") %>% unlist()
caps_len = ifelse(length(caps) == 0, NA, max(nchar(caps)))
caps = caps[nchar(caps) == caps_len] %>% paste0(collapse = ", ")
return(caps)
}
result = input %>%
mutate(ans = map_chr(Words, get_longest_capital)) %>%
mutate(ans = ifelse(ans == "", NA_character_, ans))
all.equal(result$ans, test$`Expected Answer`)
# [1] TRUE
&&