Truncating text to next cell but only whole words.

Hi Guys,

Great forum!

I have a little problem and I was wondering if any of you geniuses may be able to help me?

Basically I am creating an adwords campaign in excel, however adwords state that the title can noly be 25 characters and the description 35 characters.

A lot of my titles are more than 25 characters, and I can easily truncate the cells to just display 25 chacters, however this cuts off words half way through and looks messy.

I want to be able to display 25 max characters in the first cell, and the rest in the second cell, however only truncate where there is a space.

Does this make sense?

Example:

This is a test title and it is purple has 37 characters

I need it to do this:

This is a test title and
it is purple

This seems OK, however it so happened that the 25th character was a space, what about this?

This is an extremely satisfactory product - 41 characters

The 25th chacter is the "i". So i would need it to go back to the space and then truncate to the next cell, like this:

This is an extremely
satisfactory product

Any ideas?

A donation would be gladly made if anyone can help.

Cheers,

Mike

tRY:

=TRIM(LEFT(LEFT(A1,25),FIND("^^",SUBSTITUTE(LEFT(A1,25)," ","^^",LEN(LEFT(A1,25))-LEN(SUBSTITUTE(LEFT(A1,25)," ",""))))))

Where A1 contains string

Then =SUBSTITUTE(A1,B1,"")

Where B1 contains the first formula.

Thanks, but don't worry about it. Donate it to a worthy charity instead