title: How can I substitute quotation marks in Excel with SUBSTITUTE formula?
body: <p>I have worksheet where I need named ranges to correspond to the contents of another cell.  The text in the cell is something like:</p>
  
  <pre><code>Partitions w Studs 16" oc
  </code></pre>
  
  <p>Named ranges cannot have spaces, or most importantly, special characters like ".  So, the range is named the following:</p>
  
  <pre><code>PartitionswStuds16oc
  </code></pre>
  
  <p>To change the former into a reference to the latter in the worksheet, I can handle removing the spaces with the following formula:</p>
  
  <pre><code>=SUBSTITUTE(B1," ","")
  </code></pre>
  
  <p>I cannot, however, substitute the <code>"</code> because the double-quotation mark is used to specify text in the formula.  Excel can't parse the following formula, as expected:</p>
  
  <pre><code>=SUBSTITUTE(SUBSTITUTE(B1," ",""),""","")
  </code></pre>
  
  <p>Any tips on how to get around this?  I know I could change the text to say <code>16-in.</code> instead of <code>16"</code>, but I want to keep it as my client requested if possible.</p>