Counting Strings Containing Certain Text

Some times we want to calculate how many strings contains certain text. Lets consider this example text:

shahjahankhanalibahadur
shahjahanalibahadurkhan
jahankhanalibahadurshah
shahjahankhanbahadur
jahanalibahadur

I want to find how many of above five contains the text "ali" the formula is simple!! We can use COUNTIF() with a little variation that allows us to "look into each of them for "ali" and then give count of them". Here is the formula:

=COUNTIF(A1:A5,"*"&"ali"&"*")

The result will be 04.

The same thing can be done using SEARCH() with following Array Formula:

=SUM(IFERROR(SEARCH(C1,A1:A5)>=1,0)*1)

This formula seraches for "ALI and gives an array of true & falses and errors, where it find or does not find the match. In order to avoid error message, the IFERROR() function is used to replace them with zeros. When these are multiplied with 1, it assure that all TRUE are replaced by one and then they are summed up by SUM() to give us 04 as a result.

Here is the Sample File

Triming a Text without a workbreak

Sometimes it is desired to trim a sentence to a given length so that i can be accommodated into a a cell. Lets say we have a sentence but we want to restrict its length to 80 characters. A solution could be to use LEFT() function that will take Text & Text Length as arguments and will furnish a 80 character long Text String but what if the 80th character ends up in middle of a word?  Lets understand it with an example, take the following sentence:

"MY NAME IF FASEEH AND I AM LEAVING FOR TEXAS. I WILL BE TRAVELING BY A LOCAL TRAIN THAT WILL TAKE 08 HRS TO REACH BOSTON."

This sentence has 121 characters including spaces. If i use LEFT() to find the first 80 characters, it will return following text:

"MY NAME IF FASEEH AND I AM LEAVING FOR TEXAS. I WILL BE TRAVELING BY A LOCAL TRA"

You see that the sentence does ends "naturally" rather it is terminated in middle of a word. The ideal solution would have been either to include the complete word "TRAIN" or stop before the word starts. In case we want to stop before the word "TRAIN" started (that should be the case because we restricted to limit of 80 characters) the length of the sentence would have been 76 characters instated of 80. (The " TR" portion being excluded}.

The following formula assured that "Word at the end of the sentence is not got chewed up by our formula"

=IF(LEN(A2)<=80,A2,LEFT(A2,MATCH(80,IF(MID(A2,ROW($A$1:$A$250),1)=" ",ROW($A$1:$A$250)))-1))

Here is the Sample Workbook