Reputation: 23
I used this formula to split each word in different row:
=ARRAYFORMULA(TRIM(QUERY(SPLIT(FLATTEN(
IF(Sheet1!A2:B6000="",,Sheet1!A2:A6000&"×"&SPLIT(Sheet1!B2:B6000, " "))), "×"),
)))
But it is working only for the first two words in each cell in column A.
How can I update this formula to split all words?
Here is the sheet sample: https://docs.google.com/spreadsheets/d/1wi5neSYC_lb6nRXDRYxMk7tLWCoBpy2VSx7ejXt9yXI/edit?usp=sharing
Upvotes: 0
Views: 76
Reputation: 30271
added formula to your sheet:
=QUERY(INDEX(SPLIT(FLATTEN(A2:A100&"|"&BYROW(B2:B100,LAMBDA(bx,SPLIT(bx," ")))),"|",0,0)),"SELECT * WHERE Col2!=''")
Upvotes: 2