Split text in single cell to multiples of 80 characters by full words

Try this formula in B1 and pull it to the right until you see blanks

=TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",255)),(COLUMNS($A:A)-1)*255+1,255))

Row\Col	A	B	C	D	E	F	G
1	The Store Owner Is Really Creepy	The	Store	Owner	Is	Really	Creepy

I resorted to using a UDF (user defined Function) "RngConcat()" to concatenate ranges.

In C2, the formula is simply
=IFERROR(TRIM(LEFT($B$2,FIND(" ",$B$2,LOOKUP(81,FIND("*",SUBSTITUTE($B$2," ","*",ROW($A$1:$A$18))))))),$B$2)

In D2 copied to the right it's
=IF(LEN(REPLACE($B$2,1,LEN(RngConCat($C$2:C$2))+COLUMNS($A$1:A1)*2,""))<=80, REPLACE($B$2,1,LEN(RngConCat($C$2:C$2))+COLUMNS($A$1:A1)*2,""), TRIM(LEFT(REPLACE($B$2,1,LEN(RngConCat($C$2:C$2))+COLUMNS($A$1:A1)*2,""),FIND(" ",REPLACE($B$2,1,LEN(RngConCat($C$2:C$2))+COLUMNS($A$1:A1)*2,""),LOOKUP(81,FIND("*",SUBSTITUTE(REPLACE($B$2,1,LEN(RngConCat($C$2:C$2))+COLUMNS($A$1:A1)*2,"")," ","*",ROW($A$1:$A$25))))))))

In a VBA module, use this code

Please Login or Register  to view this content.

See attachment. I used your formula in row 1, mine in row 2

Hi.

Assuming the string of text is in A1, then, in B1:

=MID($A1,CHOOSE(1+(COLUMNS($A:A)=1),SUMPRODUCT(LEN(A1:$B1))+1,1),LOOKUP(2,1/(1-ISNUMBER(MATCH(MID(UPPER($A1),ROW(INDIRECT(CHOOSE(1+(COLUMNS($A:A)=1),SUMPRODUCT(LEN(A1:$B1))+1,1)&":"&CHOOSE(1+(COLUMNS($A:A)=1),SUMPRODUCT(LEN(A1:$B1))+80,80))),1),INDEX(CHAR(ROW(INDIRECT("65:90"))),,),0))),ROW(INDIRECT(CHOOSE(1+(COLUMNS($A:A)=1),SUMPRODUCT(LEN(A1:$B1))+1,1)&":"&CHOOSE(1+(COLUMNS($A:A)=1),SUMPRODUCT(LEN(A1:$B1))+80,80))))-CHOOSE(1+(COLUMNS($A:A)=1),SUMPRODUCT(LEN(A1:$B1)),0))

Copy to the right until you start to get blanks for the results.

Regards