Character/Word count

[stapuff]

in B9 I have text (could be whatever length)

in S9 I need to return all or part of B9 depending on character or word count of B9

condition 1 - If B9 word count < 55 and character count < 140 the return all of B9

condition 2 - If B9 word count < 55 but character count > 140 then return the first 140 characters that are full words

condition 3 - If B9 word count > 55 but character count < 140 then return the first full 55 words.

condition 4 - If B9 word count > 55 and character count > 140 the return the first 140 characters that
are full words


I get the concept of the formula needed....trying to figure out how to return back "full" words in condition 2,3,4

Here is what I have so far in S9...


=IF(AND(LEN(B9)<140,LEN(TRIM(B9))-LEN(SUBSTITUTE(B9," ",""))+1<55),B9,IF(AND(LEN(B9)>140,LEN(TRIM(B9))-LEN(SUBSTITUTE(B9," ",""))+1<55),LEFT(B9,140),""))

Your thoughts would be greatly appreciated.

stapuff

 

[kpark91]

I am struggling to understand what you mean by "full" words.

 

[xenou]

My take is that the logic can be reduced (if we define full words as any text separated by spaces, and make the simplifying assumption that no words are 140 characters long - as few are, in English at any rate).

In short, we can say the rule is "give me the the full words, up to at most 140 characters".

Here's my effort:
In cell A2:
When most I wink, then do mine eyes best see, For all the day they view things unrespected; But when I sleep, in dreams they look on thee
In cell B2:
=SUBSTITUTE(LEFT(A2,140)," ",REPT(" ",50))
In Cell C2 (this is the result):
=SUBSTITUTE(TRIM(LEFT(B2,LEN(B2)-50)),REPT(" ",50)," ")
In Cell D2 (also the result, without using a helper column):
=SUBSTITUTE(TRIM(LEFT(SUBSTITUTE(LEFT(A2,140)," ",REPT(" ",50)),LEN(SUBSTITUTE(LEFT(A2,140)," ",REPT(" ",50)))-50)),REPT(" ",50)," ")


Result in C2 and D2 is:
[When most I wink, then do mine eyes best see, For all the day they view things unrespected; But when I sleep, in dreams they look on]

Not very well tested.
Sample workbook with a little bit of data:
<a href="http://northernocean.net/etc/mrexcel/20110706_wb.zip">Sample Workbook</a>
sha256sum: 8a1bb312c8c599f273a6634c7c5dbc963db304efacda6a38283a0d914a9f832f

 

[Jonmo1]

Perhaps...

=TRIM(LEFT(A2,LOOKUP(140,SEARCH(" ",A2&" ",ROW(A1:A140)))))

 

[Jonmo1]

My last post accounts for all criteria except #3

condition 3 - If B9 word count > 55 but character count < 140 then return the first full 55 words.


However, let's think about this..

In order to meet that criteria, at a minimum the cell would need

At least 25 1 letter words like A and I (do you know any other 1 letter words?)
AND
No more than 30 2 letter words (or any other words combining for 90 characters including the spaces)


What are the realistic odds of that occuring?



If you still really need to account for that possibility, then this should cover it

=TRIM(LEFT(SUBSTITUTE(LEFT(A2,LOOKUP(140,SEARCH(" ",A2&" ",ROW(A1:A140))))," ",REPT(" ", LEN(A2)),55),LEN(A2)))

 

[xenou]

Looks better (I was actually losing information) ... and I'm in over my head

ξ

 

[stapuff]

I appreciate all the effort on this post.

Xenou - the "in over my head" feeling I know and understand well.

Jonmo1 - come on.....you know you just have to build it in.

The just of this is - I am automating posting to wordpress blog from excel. The blog auto posts to twitter, facebook, etc. In wordpress the excerpt can only be 55 words or less and twitter only allows a max of 140 characters so those are the criteria I need to fit within.

I agree with the assumed statement a "full" word is surrounded by spaces. In Excel "1" can be considered a "word" if it is in a string. Example - "Step 1 - turn the grill on high"

The following example in B9 (minus the quotes) is 322 characters and 60 word count.

In my criteria listed in my initial post since both are over the max allowable..... the 140 character count needs to be returned to cell S9.

"My goals are to loss 92 lbs in 1 year and finally see my ab's. Lofty goals I understand, but something I was really close to achieving before. I will be posting initial pics and weight/measurements info next week along with my specific plan on "how" to lose weight. I base my plan around something I call a fire triangle."

If I did not look for what has been called "full" words the following is what 140 characters / 28 word count would look like (minus the quotes)

"My goals are to loss 92 lbs in 1 year and finally see my ab's. Lofty goals I understand, but something I was really close to achieving befor"

The "befor" on the end does not make sense so I would want that trimmed off.

The final result being "My goals are to loss 92 lbs in 1 year and finally see my ab's. Lofty goals I understand, but something I was really close to achieving"



Regardless - I still appreciate your time and efforts.

stapuff

 

[Jonmo1]

Both formulas I proved give that result

Excel Workbook
	A
2	My goals are to loss 92 lbs in 1 year and finally see my ab's. Lofty goals I understand, but something I was really close to achieving before. I will be posting initial pics and weight/measurements info next week along with my specific plan on "how" to lose weight. I base my plan around something I call a fire triangle.
3	My goals are to loss 92 lbs in 1 year and finally see my ab's. Lofty goals I understand, but something I was really close to achieving
4	My goals are to loss 92 lbs in 1 year and finally see my ab's. Lofty goals I understand, but something I was really close to achieving
Sheet1

 

[stapuff]

I need to re look at your solution.

I ended up figuring it out the long way....your solution appears to be far easier to read...at the least.

=IF(AND(LEN(TRIM(B9))-LEN(SUBSTITUTE(B9," ",""))+1<55,LEN(B9)<140),B9,IF(OR(AND(LEN(TRIM(B9))-LEN(SUBSTITUTE(B9," ",""))+1<55,LEN(B9)>140),AND(LEN(TRIM(B9))-LEN(SUBSTITUTE(B9," ",""))+1>55,LEN(B9)>140)),LEFT(B9,FIND(CHAR(7),SUBSTITUTE(LEFT(B9,140)," ",CHAR(7),(140-LEN(SUBSTITUTE(LEFT(B9,140)," ","")))))-1),LEFT(B9,FIND(CHAR(7),SUBSTITUTE(B11," ",CHAR(7),55)))))

as before thanks for your time and effort in helping me,

stapuff

 

[stapuff]

Jonmo1 -

Thanks for the help...your suggestion works great and has an added benefit over mine ....your accepts special characters like !@#$^&(* where mine errors out.

Thanks again,

stapuff