Home » List Longest Words in Sentence

List Longest Words in Sentence

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
  Result
Power 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
  Result
Power 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
  Result
Power 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
                    
                  

Leave a Reply