title: How to determine if a cell formula contains Constants?
body: <p>When debugging or Quality Checking an Excel report at work I have found that the problem was because of text being hard coded inside a formula. I have heard this as being a Constant and Formula mixed cell.</p>
  
  <p>Here are examples of what I see.</p>
  
  <p>Constant =<code>100</code></p>
  
  <p>Constant =<code>Facility</code></p>
  
  <p>Formula cell <code>=INDIRECT(ADDRESS(5,MATCH($A7&amp;$B7&amp;C$2,Data!$4:$4,0),,,$A$2))</code></p>
  
  <p>Mixed cell <code>=INDIRECT("Data!"&amp;ADDRESS(5,MATCH($A7&amp;$B7&amp;C$2,Data!$4:$4,0)))</code></p>
  
  <p><code>"Data!"</code> is the Constant in the mixed cell, in this case the sheet name. If that sheet name ever changed, the formula would break. I have found and am using two conditional formats to highlight cells that are Constants and those that are formulas using this <a href="http://j-walk.com/ss/excel/usertips/tip045.htm" rel="nofollow noreferrer">"Identify formulas using Conditional Formatting"</a>. I need to come up with a way to format those cells which contain these Constants inside of formulas.</p>
  
  <p>I have <a href="https://stackoverflow.com/questions/18847431/how-to-search-for-in-string-excel">found this question</a> and tried using <code>=IF(COUNT(SEARCH(CHAR(34),A1,1)),TRUE,FALSE)</code> and <code>FIND()</code> to see if I could check if a cell had double quotes inside of it, but the SEARCH() returns <code>FALSE</code> since it is looking at the cells value and not it's contents. It returns <code>TRUE</code> if the cell contains <code>"Constant"</code> but if it is a formula it returns <code>FALSE</code>, such as if the cell contains <code>="Constant"</code>.</p>
  
  <p>How can I find Constants inside formulas across a whole worksheet or workbook?</p>
  
  <h2>EDIT*</h2>
  
  <p>Thanks to Sidd's code below I have made a function in a module I can use in conditional formatting to at least highlight cells that contain quotes inside the cells.</p>
  
  <pre><code>Function FormulaHasQuotes(aCell)
  
    If InStr(1, aCell.Formula, """") Then
        FormulaHasQuotes = True
    Else
        FormulaHasQuotes = False
    End If
  
  End Function
  </code></pre>
  
  <p><img src="https://i.stack.imgur.com/lUcWo.png" alt="FormulaHasQuotes Conditional formatting pic"></p>