Excel "Trim" question

[96ukssob]

I'm trying to figure something out but I'm stumped...

I have a line of text that is between 20 and 70 characters long. What I want to do is Trim the line so it is no longer than 35 characters, but also so that is trims ONLY from the last space. This way it does not cut off a word, only puts in a break right before it.

Also, so it will take what is left over and move it to the cell beside it, so I would now have two cells of data instead of one.

help?

[deef-thp]

This is opposed to merging adjacent cells?

[96ukssob]

This is opposed to merging adjacent cells?

The data is in one column now, but it needs to be broken out into two columns with a max character limit on the first one but making sure to trim the last space in the sentence so words are not cut off.

[deef-thp]

.. because the goal is to display a bunch of text on in a single row, without screwing up your column widths?

[96ukssob]

.. because the goal is to display a bunch of text on in a single row, without screwing up your column widths?

actually what I am doing is trying to copy ads from a Yahoo campaign to AdWords and they have 100's of ad creative. So Yahoo allows you to have one line at 70 characters and AdWords is 2 lines at 35 characters.

I have this forumla, but trims off the last word, I was hoping it would trim the space before the 35th character:

=LEFT(C2,FIND(CHAR(35),SUBSTITUTE(C2," ",CHAR(35),LEN(C2)-LEN(SUBSTITUTE(C2," ","")))))

[Adraco]

Look what I found among my own Excel bookmarks:
http://groups.google.com/group/micro...2bf93c8d?pli=1

As far as I understand, this is what you are looking for?

[96ukssob]

Look what I found among my own Excel bookmarks:
http://groups.google.com/group/micro...2bf93c8d?pli=1

As far as I understand, this is what you are looking for?

Nice find! pretty much, the only thing i need it to do is whatever was before that 35 characters to put that on another line