excel - How to slip text into new paragraph after every n words

Question

I am working in excel . Say i have a text --> one two three four five six seven eight nine ten . Now if i want to change paragraph line after every 3 words -

One two three

Four five six

Seven eight nine

Ten

Tell me if this is possible in excel . Or if there is another way to do it

Answer 1

It's 'OK' to do in Excel I'd guess. But you'd need CONCAT which comes with ExcelO365 or Excel 2019. And it would be handy to have ExcelO365 alltogether to use SEQUENCE. For example:

Formula in A2:

=CONCAT(IF(MID(A1,SEQUENCE(LEN(A1)),1)=" ",IF(MOD(LEN(LEFT(A1,SEQUENCE(LEN(A1))))-LEN(SUBSTITUTE(LEFT(A1,SEQUENCE(LEN(A1)))," ","")),3)=0,CHAR(10),MID(A1,SEQUENCE(LEN(A1)),1)),MID(A1,SEQUENCE(LEN(A1)),1)))

At the centre of the formula is MOD which tells if the placement of any space is in the table of three. If so, we replace it with a newline before we concatenate the string back together.