Of course you cannot.

If you have 60 characters, and you split them at character 25 you will
have 35 characters in your second cell, thereby failing your 30
character limit.

Assuming then that you have 50 characters to split 30-30, you have a
problem with the word that you do not wish to split, if it is 10 (or
more) characters long and the last character is in position 30 you need
to find the space that is 11 characters back, and there could be 3 or 4
spaces there on other lines.

For a 'best try' you could use two helper columns and extract
characters 25 to 33 into there,
=Mid(A1,25,8)
find the first space
=IF(B2<"",FIND(" ",B2),0)
and use that to split to the first split-cell
=Left(A1,(30-6+c1))
and the second split-cell
=Mid(a1,(30-6+c1),40)

This will split most cells on a 5-character-back or 3-character-forward
test, but will fail on large words.


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=385004