Formula to Spit up Cells based on words and character length

[excellearner9]

I need a function that can split up cells based on a character length but will also look at the words so that it does not split up a word in half due to a character limitation. For example, lets say I split up the sentence below into two cells in a row based on 35 characters.

I am creating this sentence as a Test String Example for Demonstration purposes.

What happens is it splits the word Test in half since the 'e' in test is the 35th character. I want to be able to split the sentence at 35 characters, but I want it to recognize test as a word and move the entire word into the 2nd cell. Then cut off the sentence at the last full word which is the letter/word "a" within that 35 character limit.

I am creating this sentence as a Te st String Example for Demonstration purposes.

I want to be able to split the sentence at 35 characters, but I want it to recognize test as a word and move the entire word into the 2nd cell. Then cut off the sentence at the last full word which is the letter/word "a" within that 35 character limit.

I am creating this sentence as a Test String Example for Demonstration purposes.

Is there a function to do that?

[NBVC]

Have a look at this recent thread... sounds similar:

http://www.excelforum.com/excel-prog...ole-words.html

just change the 25's to 35's

[excellearner9]

Thank you! That is perfect!

[excellearner9]

Using my example above and your formula from another post, it now puts the word "Test" in the second cell but with a space in front of the word "Test". Is there a way to remove the space in the same formula?

[NBVC]

Enclose the formula within a TRIM() function.

[excellearner9]

Thank you for your help

[HAYRYA]

NBVC

I don't not understand your closing formula ( then =SUBSITUTE(A1,B1"") / The first part worked perfect. Can you write out the formula to retrieve the following text greater than 35 for cell C1?

Thanks, Ryan

[protonLeah]

HAYRYA,
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.