Can someone suggest a formula in Excel (2013) that will break a long string of text (like a sentence or paragraph) located in cell A1 into blocks (in column B) which:
a) each have a length of < 50 characters and
b) each are broken at the last space before 50 in order to avoid divided words?
For example, suppose the entry in cell A1 is as follows:
I receive lots of great help and solutions at Experts Exchange. It is a great resource and well worth the monthly fee.
This entry contains 118 characters and if broken into exactly 50-character segments in column B, would appear as follows (with the number of characters shown in parentheses to the left):
(50) B1 = I receive lots of great help and solutions at Expe
(50) B2 = rts Exchange. It is a great resource and well wort
(18) B3 = h the monthly fee.
I'm looking for a formula to paste in column B which, instead of inserting the break at exactly 50 characters, will identify and create the breaks at the last space just shy of 50:
(46) B1 = I receive lots of great help and solutions at
(50) B2 = Experts Exchange. It is a great resource and well
(22) B3 = worth the monthly fee.
Note that when the breaks are made, the space at those locations is not lost. In the above example, a space is present after the words "at" and "well" in the first two segments respectively.
Also, it's clear that the formula will have to take into account the number of characters which end up up in each of the preceding cells.