Need to truncate a cell if the character length is over 100

Hello

Can somone help me figure out how to do the following?

Need to check if a cell is over 100 characters and if so, truncate what is over 100, insert a new cell underneath it and, append the excess to the new cell.

It needs to make sure to preserve whole words (making sure not to break a word while truncating).

Any help appreciated.

This may help for the first part?

Please Login or Register  to view this content.

That works to add the line and move extra characters. Thank you for that.

But,

How do I make it not split words? For example, right now with your code:

"7.5 If PLC power is lost during the test, power down UPS, replace battery and or UPS unit as required." becomes

"7.5 If PLC power is lost during the test, power down UPS, replace battery and or UPS unit as require
d."

I need it to become something like:

"7.5 If PLC power is lost during the test, power down UPS, replace battery and or UPS unit as
required."

Thanks for any help

Can attach a file with some samples? To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.

Ok here is an attached workbook with 2 tabs (Unmodified, and After Macro). Truncation.xls

I managed to hobble together some code from bits and pieces that will pull the last word off of a cell value, insert a row underneith and place the word in the new row.

Please Login or Register  to view this content.

This is the beiginning I guess... I still need to figure out how to add some math functions. Need to see if 1 word pulled out of the string will bring the length down to 100 characters, and if not, take off more words as needed. Also need to check the newly created cell's length to see if the appended words will cause that row to go over 100 characters and adjust.

Any help appreciated.

Well to continue further into my problem, I figured out how to do a bit of this:

Please Login or Register  to view this content.

Unfortunately It only works once. Furthing executions of the macro only inserts the line. Do I need to somehow empty the split array before I can fill it again?