Count specific/part of a text in a cell

Is there a way to count part of a text in a cell. Say I had a column with a different sentence in each row. Is it possible for me to use a formula to find out how many times a "the" appears anywhere in the column; or is it possible to find out how many times the string of text "ch. Ab" appears; or even the times that the combination "ing" appears?

Also on a somewhat unrelated note; I know that you can add text together but is there a way to split it apart using a formula. Say I had Surname, Firstname and I wanted to put Surname in a cell and Firstname in a different cell?

Any help would be greatly appreciated, Thanks.

With your sentences in column A, use this to count "the":

=COUNTIF(A:A,"*"&"the"&"*")

or you could put "the" in a cell somewhere (B1 - without the quotes), and the formula would become:

=COUNTIF(A:A,"*"&B1&"*")

then you could put any string in B1. The asterisk is a wildcard character, i.e. any number of characters. This might mis-count - e.g. it will count "Theatre", so you might want to put spaces around the word " the ".

Hope this helps.

Pete

Thanks guys! They both work great. Pete_UK's is simpler so I think I will use it for what I need right now but dilipandey's seems to be a bit more powerful if I have more complex applications later. I appreciate the help.

Is there a way to count how many times both "the" and "ing" show up in the same sentence? Just the times that they BOTH show up and they aren't necessarily right next to eachother in the sentence. Thanks

You can do this:

=COUNTIFS(A:A,"*the*",A:A,"*ing*")

or put the words in cells and refer to them:

=COUNTIFS(A:A,"*"&B1&"*",A:A,"*"&C1&"*")

COUNTIFS allows you to set up multiple conditions (ANDed together), whereas COUNTIF is for a single condition.

Hope this helps.

Pete

Thanks, again it works great. I actually tried putting an AND formula in there and it didn't work. This is much easier anyways