title: Formula in Excel to count occurrences of substrings within strings
body: <p>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.</p>
  
  <p>The column of string data (tweets) looks like this:</p>
  
  <pre><code>   A
  1  An example string with @username in it
  2  RT @AwesomeUser says @username is awesome
  </code></pre>
  
  <p>The column with "substrings" (Twitter screen names) looks like this:</p>
  
  <pre><code>   B
  1  username
  2  AwesomeUser
  </code></pre>
  
  <p>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".</p>
  
  <p>I can't do it this way:</p>
  
  <pre><code>=COUNTIF(A:A, "username")
  </code></pre>
  
  <p>because COUNTIF only looks for strings, not substrings. This formula would always return "0".</p>
  
  <p>Here's a <a href="http://www.excelforum.com/excel-formulas-and-functions/341605-solved-counting-the-number-of-times-a-word-appears-in-a-worksheet.html" rel="noreferrer">formula</a> I thought might do it:</p>
  
  <pre><code>=SUMPRODUCT((LEN(A:A)-(LEN(SUBSTITUTE(A:A,"username",""))))/LEN("username"))
  </code></pre>
  
  <p>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).</p>
  
  <p>I thought about using:</p>
  
  <pre><code>=COUNTIF(A:A, "*username*")
  </code></pre>
  
  <p>but COUNTIF requires a string with the star operators; I need to use cell references due to the volume of data.</p>
  
  <p>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)?</p>
  
  <p>I know that I <em>could</em> parse the data, but I would like to know how to do it in Excel.</p>