Help on splitting a 60 character cell into 40 character max chunks, preserving whole words

Hello!!

My first post so please be gentle! I've had a browse around for an answer for my conundrum on here with little or no success!

My problem. I have a cell in Excel that contains some words that amount to 60 characters in length. I need to split this into 40 character max chunks, and if possible, avoid splitting words across the cells.
I've tried a few suggestions posted to this forum for people with similar problems, but can't seem to customise the formula for my own requirements.

I'm not hot on VBA at all, so if a standard Excel function/functions could solve my issue, that would be amazing.

I have attached a piece of sample data to better explain my woes! The text in column B needs splitting out so that I have a cell with 40 or less characters in it, with the remainder text in the next field, with, if possible, no word breaks.

I appreciate your help on this!

Tim.

I know this doesn't take into account "NOT SPLITTING" words but its a start.

Column D:

Formula:

Please Login or Register  to view this content.

Column E:

Formula:

Please Login or Register  to view this content.

AlKey, you sir are a gent! That suggestion solved an issue that's troubled me many many times!!! I can't thank you enough!

I guess what I should do here is ask you, if you don't mind, to explain in brief terms, what the formula does so at least I can try and understand how it does it!

Once again, many many thanks!

Cheers,
Tim.