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...
It's good now.
I got this in the email from the forum =LEFT(K2,FIND(" ",K2,200)). Thats what I used and it left the space.
When I came to post that it "didn't work" I noticed the formula had a -1 in it. =LEFT(K2,FIND(" ",K2,200)-1) Tried it and it worked fine!
So maybe the poster went back and corrected, who knows!
Guilty as charged. I remembered after the fact that it would return everything through the cell it found rather than up to, just a brain fart on my end.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks