Mergen
Mergen

Reputation: 23

SPLIT EACH WORD IN NEW ROW

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

this the image

Upvotes: 0

Views: 76

Answers (1)

rockinfreakshow
rockinfreakshow

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!=''")

enter image description here

Upvotes: 2

Related Questions