Split a sentence into specifi number of words as defined

Hi All,

request you to please help on my request.

For example below sentence : i want it to break it to 4 word's each (similar to bigram, trigram.. i need 4gram), also need an option where i can input specific number of words to be split into cells.

Ex : I feel very fortunate to be part of to be part of of the company for the great opportunities it offers and it would make me feel good that friends or colleagues can be part of what I am now

need as : I feel very fortunate
to be part of
of the company for the
great opportunities it offers

............and so on into adjacent cells 4 words each split.

i have millions of rows, i am trying to make a meaningful word cloud for sentiment analysis viz.

Originally Posted by rincyroyal

i have millions of rows

Given that a single sheet only holds just over 1 million rows, I somehow doubt that your claim is correct. Using excessive exaggeration to describe your problem is not helpful to anyone trying to help you.

With your text in A1, enter this regular formula in B1

=LEFT(A1,FIND("|",SUBSTITUTE(A1," ","|",4))-1)

and this Array formula in C1

=IF(B1="","",LEFT(REPLACE($A1,1,SUM(LEN($B1:B1),COUNTA($B1:B1)),""),IFERROR(FIND("|",SUBSTITUTE(REPLACE($A1,1,SUM(LEN($B1:B1),COUNTA($B1:B1)),"")," ","|",4))-1,LEN($A1))))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Once array confirmed, drag right and fill down as needed.

Thanks you all for the quick solutions, truly appreciate you r quick responses, will try and test

Adding one more option for 2 separate formulas for B2 and C2 across:

Assuming your data in A2, in B2, enter and copied down.

=LEFT(A2,FIND("^",SUBSTITUTE(A2," ","^",4))-1)

Then, in C2, enter and copied across and down.

=TRIM(LEFT(SUBSTITUTE(MID($A2,SUMPRODUCT(LEN($B2:B2))+COLUMNS($B1:B1)+1,255)," ",REPT(" ",LEN($A2)),4),LEN($A2)))

Both methods from my Post #5 and this one included in new attached sample.

AWESOME.. !! this is what i wanted...!

now i can define what i want.

thank you all masters..!

Originally Posted by rincyroyal

AWESOME.. !! this is what i wanted...!

now i can define what i want.

You're welcome, glad it works for you.

Add rep would be appreciated, click the "Star" at the lower left corner of the posts you found helpful.

If your query has been "Solved", please mark the thread as such, Thank you.

Sorry for the delayed response, surely will do that.

However i have a request within the solution, how do i increase the sentence word count, for instance this formula which you gave :
=TRIM(MID(SUBSTITUTE(" "&$A1," ",REPT(" ",300)),(4*COLUMNS($A:A)-3)*300,1200)) splits by 4 words, how can i increase to 5, 6..etc, also decrease by 3,2, etc.

Request you to please add a little explanation.

Excellent Solution. It WORKS MORE than what i NEEDED..!

Hi Master, i get a new error, perhaps i was not clear in the requirement, sorry about that.

The formula returns error when the cell contains less number of words than the inputted/reference cell number (considered to split)

splitcell.png

Please find the reference