Provide a formula to list longest words in sentences given in A2:A10. For example in case of “Spill the beans”, the answer would be “Spill, Beans” as both are the longest in this sentence.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 19
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of List Longest Words in Sentence with Power Query
Power Query solution 1 for List Longest Words in Sentence, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Result = Table.AddColumn(
Source,
"Split",
each Text.Combine(
[
a = Text.Split([Sentence], " "),
b = List.Max(List.Transform(a, (f) => Text.Length(f))),
c = List.Select(a, (f) => Text.Length(f) = b)
][c],
", "
)
)
in
ResultPower Query solution 2 for List Longest Words in Sentence, proposed by Excel BI:
Welcome to GitHub!!! Great code and it worked like a charm.
1st is each _ and 2nd is List.Transform(_
Group2 = Table.Group(Filtro, {"Índice"}, {{"Tabela", each _, type table [Índice=number, Sentence=nullable text, Personalizar.1=nullable number, Personalizar=number, Personalizar.2=number]}}),
Result = Table.TransformColumns(Tab2, {"Sentence", each Text.Combine(List.Transform(_, Text.From), ","), type text})[[Sentence]]
Power Query solution 4 for List Longest Words in Sentence, proposed by Venkata Rajesh:
let
Source = Data,
#"Expected OutPut" = Table.AddColumn(
Data,
"Expected",
each
let
_Max = List.First(
List.Sort(
List.Transform(Text.Split([Sentence], " "), each Text.Length(_)),
Order.Descending
)
)
in
Text.Proper(
Text.Combine(List.Select(Text.Split([Sentence], " "), each Text.Length(_) = _Max), ", ")
)
)
in
#"Expected OutPut"Power Query solution 5 for List Longest Words in Sentence, proposed by Sergei Baklan:
let
Source = Excel.CurrentWorkbook(){[Name = "range"]}[Content],
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
Result = Table.FromColumns(
{
List.Transform(
#"Promoted Headers"[Sentence],
each [
w = Text.Split(Text.Proper(_), " "),
m = List.Max(List.Transform(w, each Text.Length(_))),
a = List.Select(w, each Text.Length(_) = m),
t = Text.Combine(a, ", ")
][t]
)
},
{"Result"}
)
in
ResultPower Query solution 6 for List Longest Words in Sentence, proposed by Melissa de Korte:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Result = Table.AddColumn(
Source,
"Result",
each [
l = Text.Split([Sentence], " "),
len = List.Transform(l, Text.Length),
i = List.PositionOf(len, List.Max(len), Occurrence.All),
r = Text.Combine(List.Transform(i, each Text.Proper(l{_})), ", ")
][r]
)
in
ResultPower Query solution 7 for List Longest Words in Sentence, proposed by Hristo Tsenov:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
#"Split Column" = Table.SplitColumn(
#"Index",
"Sentence",
Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv),
{"Sentence.1", "Sentence.2", "Sentence.3", "Sentence.4"}
),
#"Unpivot" = Table.UnpivotOtherColumns(#"Split Column", {"Index"}, "Attribute", "Answer"),
#"Added Custom" = Table.AddColumn(#"Unpivot", "Length", each Text.Length([Answer])),
#"Grouped Rows" = Table.Group(
#"Added Custom",
{"Index", "Length"},
{
{
"Data",
each _,
type table [Index = number, Attribute = text, Answer = text, Length = number]
},
{"Answer", each Text.Combine([Answer], ", "), type text}
}
),
#"Sort" = Table.Sort(#"Grouped Rows", {{"Length", Order.Descending}}),
#"Index2" = Table.AddIndexColumn(#"Sort", "Index.1", 1, 1, Int64.Type),
#"Table Distinct" = Table.Distinct(#"Index2", {"Index"}),
#"Sort2" = Table.Sort(#"Table Distinct", {{"Index", Order.Ascending}}),
#"Capital Words" = Table.TransformColumns(#"Sort2", {{"Answer", Text.Proper, type text}}),
#"Select Columns" = Table.SelectColumns(#"Capital Words", {"Answer"})
in
#"Select Columns"Power Query solution 8 for List Longest Words in Sentence, proposed by Chandeep Chhabra:
let
words = List.Transform(Text.Split([Sentence], " "), each Text.Proper(_)),
maxlength = List.Max(List.Transform(words, each Text.Length(_))),
longestword = List.Select(words, each Text.Length(_) = maxlength)
in
Text.Combine(longestword, ", ")Solving the challenge of List Longest Words in Sentence with Excel
Excel solution 1 for List Longest Words in Sentence, proposed by Rick Rothstein:
=BYROW(A2:A10,LAMBDA(A,LET(S,TEXTSPLIT(A," "),L,LEN(S),PROPER(TEXTJOIN(", ",,FILTER(S,L=MAX(L)))))))
Excel solution 2 for List Longest Words in Sentence, proposed by محمد حلمي:
=PROPER(BYROW(A2:A10;LAMBDA(a;TEXTJOIN(", ";;IF(MAX(LEN(TEXTSPLIT(a;" ")))=(LEN(TEXTSPLIT(a;" ")));TEXTSPLIT(a;" ");"")))))
Excel solution 3 for List Longest Words in Sentence, proposed by محمد حلمي:
=MAP(A2:A10,LAMBDA(A,
LET(
S,TEXTSPLIT(A," "),
X,LEN(S),
TEXTJOIN(", ",,PROPER(FILTER(S,MAX(X)=X))))))
Excel solution 4 for List Longest Words in Sentence, proposed by Julian Poeltl:
=MAP(A2:A10,LAMBDA(S,LET(SP,TEXTSPLIT(S," "),L,LEN(SP),TEXTJOIN(", ",,PROPER(FILTER(SP,L=MAX(L)))))))
Excel solution 5 for List Longest Words in Sentence, proposed by Aditya Kumar Darak 🇮🇳:
= BYROW(
A2:A10,
LAMBDA(
a,
LET(
_splt,
TEXTSPLIT(a, " "),
_len,
LEN(_splt),
TEXTJOIN(
", ",
TRUE,
PROPER(IF(_len = MAX(_len), _splt, ""))))))
Excel solution 6 for List Longest Words in Sentence, proposed by Timothée BLIOT:
=BYROW(A2:A10,LAMBDA(r,TEXTJOIN(", ",TRUE,LET(
words,TEXTSPLIT(r," "),
lengths,LEN(words),
FILTER(words,lengths=MAX(lengths))
))))
Excel solution 7 for List Longest Words in Sentence, proposed by Hussein SATOUR:
=BYROW(A2:A10,
LAMBDA(S, LET(a, TEXTSPLIT(S," "), b, LEN(a),
ARRAYTOTEXT(FILTER(PROPER(a), b=MAX(b))))))
Excel solution 8 for List Longest Words in Sentence, proposed by Duy Tùng:
=MAP(A2:A10,LAMBDA(v,LET(a,TEXTSPLIT(v," "),b,LEN(a),ARRAYTOTEXT(FILTER(a,b=MAX(b))))))
Excel solution 9 for List Longest Words in Sentence, proposed by Abdallah Ally:
=BYROW(A2:A10,LAMBDA(a,ARRAYTOTEXT(PROPER(FILTER(TEXTSPLIT(a," "),LEN(TEXTSPLIT(a," "))=MAX(LEN(TEXTSPLIT(a," "))))))))
Excel solution 10 for List Longest Words in Sentence, proposed by Abdallah Ally:
=ARRAYTOTEXT(PROPER(FILTER(TEXTSPLIT(A2," "),LEN(TEXTSPLIT(A2," "))=MAX(LEN(TEXTSPLIT(A2," "))))))
Excel solution 11 for List Longest Words in Sentence, proposed by Sergei Baklan:
=LET(
longest, LAMBDA(str,
LET(
words, TEXTSPLIT( str, " "),
length, LEN( words ),
ARRAYTOTEXT(
PROPER(
FILTER( words, length=MAX(length) ) ) )
) ),
BYROW( data, longest)
)
Excel solution 12 for List Longest Words in Sentence, proposed by Cary Ballard, DML:
=MAP(A2:A10, LAMBDA(m, LET(b, TEXTSPLIT(m, " ", , 1), c, LEN(b), TEXTJOIN(", ", , FILTER(b, MAX(c) = c)))))
Excel solution 13 for List Longest Words in Sentence, proposed by RIJESH T.:
=MAP(A2:A10,LAMBDA(a,LET(s,a,t,TEXTSPLIT(s," "),
TEXTJOIN(", ",,IF(LEN(t)=MAX(LEN(t)),t,"")))))
Excel solution 14 for List Longest Words in Sentence, proposed by Md Ismail Hosen:
https://youtu.be/Rif0cwErCuE
Excel solution 15 for List Longest Words in Sentence, proposed by Ibrahim Sadiq:
=LET(a,TEXTSPLIT(A2," "),b,FILTER(a,(--LEN(a))=MAX(LEN(a))),TEXTJOIN(",",,b))
For single Array formula
=MAP(A2:A10,LAMBDA(x,LET(a,TEXTSPLIT(x," "),b,FILTER(a,(--LEN(a))=MAX(LEN(a))),TEXTJOIN(",",,b))))
Excel solution 16 for List Longest Words in Sentence, proposed by Nazmul Islam Jobair:
=BYROW(
A2:A10,
LAMBDA(_sent,
LET(
_words,TEXTSPLIT(_sent,," "),
_lens,LEN(_words),
_largest,FILTER(_words,_lens=MAX(_lens)),
TEXTJOIN(", ",TRUE,_largest))))
Excel solution 17 for List Longest Words in Sentence, proposed by Paul Reynolds:
=MAP(PROPER(A2:A10),LAMBDA(b,LET(a,TEXTSPLIT(b," "),c,LEN(a),TEXTJOIN(", ",,FILTER(a,c=MAX(c))))))
Solving the challenge of List Longest Words in Sentence with Python in Excel
Python in Excel solution 1 for List Longest Words in Sentence, proposed by Aditya Kumar Darak 🇮🇳:
df = xl("A1:A10", True)
def MyFun(sentence):
Max = max(Dict.values())
Filter = [w for w, l in Dict.items() if l == Max]
return ", ".join(Filter)
df["Answer"] = df["Sentence"].map(MyFun)
df
