Split Long Text Cell into Two Shorter Cells Without Splitting Word

[Guest]

I have text cells that are up to 60 characters in length that I need to break
into 2 separate cells, each cell can be up to 30 characters long without
splitting up any words. So, if the 30th character splits a word, it should
split to the second cell at the previous space.

How do I set up a formula to do this? I figured that if I wanted the first
30 characters, I could use the formula (if the text was in A1) =LEFT(A1,30).
But this splits any words.

Thanks in advance!

 

[Bryan Hessey]

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.