I'm using this function to make a short description of a product from a long description.
=MID(K2,1,200)&"…"
So it's taking the first 200 characters of cell K2 and adding a ... to it.
Whats happening is it is splitting words and ending at character 200 instead of the end of the closest word to character 200.
Is there a way to alter this formula to break at the end of a word close to character 200?
Or is there a better way to do this?
Thanks!
Try:
This should take you to the closest space after character 200
=LEFT(K2,FIND("^^",SUBSTITUTE(K2," ","^^",200-LEN(SUBSTITUTE(LEFT(K2,200)," ",""))+1))-1)
If you want to go to the closest space before character 200, take away the +1 in the formula.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Right on the money!!!!
Thanks!
A shorter way to go to the first space after 200 is =LEFT(K2,FIND(" ",K2,200)-1)
I like to complicate things...Originally Posted by darkyam
I get enough from posts from DLL reminding me that I have the same habit.It feels good to be on this end of it for a change. Cute baby.
The problem with this one is that it leaves the last space in the result so you have character 200 then a space.
Hmmm, in the email of this it was missing the -1. I think someone sneakily fixed it!
This should work.
Last edited by theblade24; 04-02-2008 at 11:45 AM.
For me it yields the same result as my formula...
but you can always use TRIM to get rid of trailing spaces
=Trim(Left(K2,Find(" ",K2,200)-1))
Note: this will get rid of all extra spaces in the string...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks