Search code examples
google-sheetslambdasplitrowregex-replace

Google Sheets: Use a Conditional Function to separate a sentence in one cell into multiple parts in subsequent cells


I want to separate a one-cell sentence into cells of 4 words each in the same row. If the sentence has more than 12 words, the 12th word should append "..." and add no more words. SPECIAL CONDITION: I cannot have a cell with only one word in it. If a sentence has five words, cell 1 should use three words, cell 2 should use two words.

enter image description here

Example data can be found here: https://docs.google.com/spreadsheets/d/1q4qRgXSq6xXhddArPCoBIuOOupRvokUd0andADCNN0Q/edit#gid=0

This is based on the question found here: Google Sheets: Split text into groups of four words: Place each group of four words into cells across the row, max 12 words / 3 columns


Solution

  • Improvising from the existing formula provided in the previous post:

    =map(A2:A,lambda(Λ,if(Λ="",,let(Σ,split(reduce(Λ,{4,7,10},lambda(a,c,substitute(a," ","|",c))),"|"),a,index(Σ,,1),b,index(Σ,,2),d,index(Σ,,3),c,if(len(iferror(index(Σ,,4))),d&"...",d),
          Δ,map({a,b,c},lambda(Ξ,counta(iferror(split(Ξ," "))))),x,index(Δ,,1),y,index(Δ,,2),z,index(Δ,,3),
          iferror(if(y=1,split(substitute(join(" ",{a,b})," ","|",3),"|"),if(z=1,hstack(a,split(substitute(join(" ",{b,c})," ","|",3),"|")),hstack(a,b,c))))))))
    

    enter image description here

    UPDATED FORMULA

    =map(A2:A,lambda(x,if(x="",,let(Λ,split(x," "),Σ,counta(Λ),
         hstack(
                join(" ",choosecols(Λ,if(Σ=6,{1,2,3,4},if(Σ>4,{1,2,3,4,5},sequence(Σ))))),
                concat(join(" ",iferror(choosecols(Λ,if(Σ=6,{5,6},if(Σ<6,,if(Σ>9,{6,7,8,9,10},sequence(Σ-5,1,6))))))),if(Σ>10,"...",))
                )))))
    

    logic : A simple if-conditional approach is used here depending on the total number of words available in the sentence. they are split first by blank space & then joined as phrases depending on the set rules of 2 Columns, 5 words per column, no column with 1 word

    enter image description here