Hi All
I am trying to split the text string in to 35 characters in each line. And if the 35th character is in between a word the string should split whole word to the next column and in next column count 35 characters and again split for next column..
I am trying an array formula in first column
=IF(LEN(A5)<=35,A5,LEFT(TRIM(A5),MAX((MID(TRIM(A5),ROW(INDIRECT("1:35")),1)=" ")*ROW(INDIRECT("1:35")))))
And
=IF(LEN(A5)<=70,SUBSTITUTE(A5,C5,""),LEFT(SUBSTITUTE(TRIM(A5),C5,""),IF(MID(SUBSTITUTE(TRIM(A5),C5,""),36,1)=" ",35,MAX((MID(SUBSTITUTE(TRIM(A5),C5,""),ROW(INDIRECT("1:35")),1)=" ")*ROW(INDIRECT("1:35")))-1)))
in subsequent columns
But this formula is not working perfectly every time as shown in the sample file please help
Regards
Mandar
I am trying to split the text string in to 35 characters in each line. And if the 35th character is in between a word the string should split whole word to the next column and in next column count 35 characters and again split for next column..
I am trying an array formula in first column
=IF(LEN(A5)<=35,A5,LEFT(TRIM(A5),MAX((MID(TRIM(A5),ROW(INDIRECT("1:35")),1)=" ")*ROW(INDIRECT("1:35")))))
And
=IF(LEN(A5)<=70,SUBSTITUTE(A5,C5,""),LEFT(SUBSTITUTE(TRIM(A5),C5,""),IF(MID(SUBSTITUTE(TRIM(A5),C5,""),36,1)=" ",35,MAX((MID(SUBSTITUTE(TRIM(A5),C5,""),ROW(INDIRECT("1:35")),1)=" ")*ROW(INDIRECT("1:35")))-1)))
in subsequent columns
But this formula is not working perfectly every time as shown in the sample file please help
Regards
Mandar