title: How to use SUBSTITUTE() for a range of values?
body: <p>Let's say that there are multiple values like such:</p>
  
  <pre><code>Address
  123 Fake Street, Philadelphia, pa
  456 Real Street, Houston, tx
  789 Soho Street, New York, ny
  </code></pre>
  
  <p>Originally, I thought I could create a lookup table to search for "pa", "tx", and "ny" so that they would get upper'd: "PA", "TX", "NY".</p>
  
  <p>Ideally, I want to write a function that would have all of the upper values in a new column. Originally, something like this:</p>
  
  <pre><code>=substitute(O33,Q31:Q35,R31:R35)
  </code></pre>
  
  <p>with <code>Q31:Q35</code> being the range of lower states, and <code>R31:R35</code> being the upper states. However, that didn't work out the way I wanted. Is there a way to do the replacement of lower case states without hard coding? I thought a lookup table would work, but it didn't.</p>
  
  <p>I want the end result to be something like this:</p>
  
  <pre><code>Address                                     New Address
  123 Fake Street, Philadelphia, pa           123 Fake Street, Philadelphia, PA
  456 Real Street, Houston, tx                456 Real Street, Houston, TX
  789 Soho Street, New York, ny               789 Soho Street, New York, NY
  </code></pre>
  
  <p>Without doing this for every row of New Address:</p>
  
  <pre><code>substitute(A2, "pa", "PA")
  substitute(A3, "tx", "TX")
  substitute(A4, "ny", "NY")
  </code></pre>