title: Search and Replace using Backrefrence in vbscript for excel 2007
body: <p>I'm using excel 2007 and i'm adding a macro that looks something like this :</p>
  
  <pre><code>Function S(Value As String, Pattern As String, ReplaceWith As String, Optional IgnoreCase     As Boolean = False)
      Dim r As New VBScript_RegExp_55.RegExp
      r.Pattern = Pattern
      r.IgnoreCase = IgnoreCase
      r.Global = True
      S = r.Replace(Value, ReplaceWith)
  End Function
  </code></pre>
  
  <p>I can use the Search and Replace function like this in the sheet :</p>
  
  <pre><code>=s("Say Hello","Hello","HI",FALSE)
  </code></pre>
  
  <p>works fine.</p>
  
  <p>What I actually want to do with this is something like this:</p>
  
  <p>Search capital letters from the text : "MyName"<br>
  Result : "My Name"</p>
  
  <p>I use [A-Z] to search can't seem to replace it with the space and with the original characters intact.</p>
  
  <p>I know i'd need to use backrefrence but can't figure out a way to do this as i'm new to both vbscript and regex.</p>
  
  <p>Thanks for your help guys! : )</p>