Formula in Excel to count occurrences of substrings within strings

Question

I am trying to count the number of times a sub-string appears within a column of string data in Excel. Please see the below example.

The column of string data (tweets) looks like this:

   A 1  An example string with @username in it 2  RT @AwesomeUser says @username is awesome 

The column with "substrings" (Twitter screen names) looks like this:

   B 1  username 2  AwesomeUser 

I want to use a formula to count the number of times that a substring from B1, B2, etc. appears in the strings in column A. For example: a formula searching for B1 would return "2" and a search for B2 would return "1".

I can't do it this way:

=COUNTIF(A:A, "username") 

because COUNTIF only looks for strings, not substrings. This formula would always return "0".

Here's a formula I thought might do it:

=SUMPRODUCT((LEN(A:A)-(LEN(SUBSTITUTE(A:A,"username",""))))/LEN("username")) 

Unfortunately, I have 16,000 entries in column B and tens of thousands in A, so counting characters won't work even on a high power PC (also, the result returned by the function is suspect).

I thought about using:

=COUNTIF(A:A, "*username*") 

but COUNTIF requires a string with the star operators; I need to use cell references due to the volume of data.

My question: does anyone know how I can use a formula for this? If using COUNTIF, how do I get a cell reference in the conditional part of the statement (or use a function to substitute the string in the cell referenced within the conditional part of a COUNTIF statement)?

I know that I could parse the data, but I would like to know how to do it in Excel.

Answer 1

You are nearly there, use

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

Caveat:
This counts the number of cells in A:A that contain the string one or more times. It does not count the total number of instances of the string.

Answer 2

A very Simple Excel Solution for occurrence of x-string ("ABC") into y-string ("ABC 123 ABC 23dfABC"):

1. Identify length of y-string. Ly = len(y)
2. Substitute x-string by x1-string (append only 1 char of any supported character to x-string. e.g. "ABC"=> "ABC_" ) and calculate length of new string y1-string. Ly1 = len(substitute(y,x,x1)).
3. No of occurrence of x-string) in y-string = Ly1-Ly

Number of Occurrence: len(substitute(y,x,x1)) - len(y)

Nomenclature for Understanding: x-string: "ABC" x1-string: "ABC_" y-string: "ABC 123 ABC 23dfABC" y1-string: ""ABC_ 123 ABC_ 23dfABC_"