Excel "Trim" question - GoFuckYourself.com

96ukssob · 04-07-2010, 02:36 PM

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 · 04-07-2010, 02:42 PM

This is opposed to merging adjacent cells?

96ukssob · 04-07-2010, 02:46 PM

Quote:

Originally Posted by deef-thp

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 · 04-07-2010, 02:48 PM

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

96ukssob · 04-07-2010, 03:01 PM

Quote:

Originally Posted by deef-thp

.. 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:

Code:

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

Adraco · 04-07-2010, 03:54 PM

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 · 04-07-2010, 04:19 PM

Quote:

Originally Posted by 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?

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

04-07-2010, 02:36 PM	#1
96ukssob So Fucking Banananananas Industry Role: Join Date: Mar 2003 Location: If I was in your ass you'd know it Posts: 12,991	Excel "Trim" question 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? __________________ Email: Clicky on Me

04-07-2010, 02:42 PM	#2
deef-thp Confirmed User Join Date: Sep 2005 Posts: 371	This is opposed to merging adjacent cells? __________________ ICQ: 6437580

04-07-2010, 02:48 PM	#4
deef-thp Confirmed User Join Date: Sep 2005 Posts: 371	.. because the goal is to display a bunch of text on in a single row, without screwing up your column widths? __________________ ICQ: 6437580

04-07-2010, 03:54 PM	#6
Adraco Confirmed User Industry Role: Join Date: May 2009 Location: Onboard an airplane around the globe Posts: 3,734	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? __________________ ---------------------------------------------------------------------------------- The truth is not affected by the beliefs, or doubts, of the majority.