title: VBA Using Regular Expressions to Split a String
body: <p>Hoping someone may be able to offer a better approach to what I am doing. Unfortunately the data and code sit on client systems so I can't share them. </p>
  
  <p>I have this long string which was previously a data table and need to split up the values into rows and columns again. The system is heavily locked down so I am limited to using VBA. The best way I can think of is to use regular expressions to identify columns and rows. I've set up my regular expression object and executed against the input string, have all the matches I need which is fine. The problem is that if I do</p>
  
  <pre><code>re = CreateObject("VBScript.RegExp")
  re.pattern = mypattern
  re.split(myString)
  </code></pre>
  
  <p>As far as I can see there is no way to retain the values I am splitting on. In some cases I want to split in the middle of the regex string anyway.</p>
  
  <p>The most promising solution I think is to do </p>
  
  <pre><code>re = CreateObject("VBScript.RegExp")
  re.pattern = mypattern
  Set matches = re.execute(myString)
  for each match in matches:
      'do something with match.firstindex
  </code></pre>
  
  <p>I considered just inserting delimiters and then using split. Unfortunately VBA seems to have no method to insert a character into a string it looks a bit clunky to use the firstindex.</p>
  
  <p>Does anyone have any thoughts on better approaches? Much appreciated if so.</p>