Complex formula: Split text into several chunks based on character count

Hi team,

I have this formula that is meant to split the source text in cell E2 into several adjacent cells (F2, G2...) where no cell should exceed 200 characters. At the same time, I'm doing a basic clean up:

Please Login or Register  to view this content.

So far so good, it's working as expected, however, it is

# splitting words across cells, e.g. "acc" in F2 and "ess" in G2. I would need to make sure that the words stay in tact and the breakpoint is essentially the last blank space before the 200th character.
# it truncates the last text block and does not output the last few words

Any idea how to solve this?

Attaching an example workbook. Any help appreciated

Originally Posted by Glenn Kennedy

Formula. F2:
=TRIM(TEXTBEFORE(LEFT(E2,200)," ",-1))

I think you should change the 200 to 201. That way, if the 200th character occurred immediately before a space character (in the 201st position), the entire 200 characters would be retained. As your formula is written now (with the 200), that last word would be "thrown away" unnecessarily. I did not try your second formula, but it looks like the same change should be made there as well.

You miss the last word :p

G2 has to be

=IF(F2="","",TRIM(TEXTBEFORE(LEFT(TEXTAFTER($E2&" ",F2),201)," ",-1)))

I've not been following this in detail, but I thought the OP had said:

... it truncates the last text block and does not output the last few words ...

so does that mean the final 83 characters (or 112 in Glenn's formula) should be omitted?

Pete

Awesome, thanks all! The formula works perfectly I'll also save the VBA, just in case...

Thanks for the super quick help!

Hi

As per my understanding we need to write a code to fulfill this requirement. There is an option with power query to separate the text into columns after 200 characters.
However, based on your requirement of complete word at the end, I have written a simple code to get the results in sheet "Final Result".
You can change the data in column 1 and run a macro to check the result in attached sheet. Let me know in case of any questions.

Thanks all! Appreciate the many answers. Went with Glenn's solution in the end, but keeping the other in the back pocket if I run into any issues. Thank you