title: excel regex macro to match zip code and move it four columns to the right
body: <p>I'm working with a big excel file that has a lot of information on businesses my company works with. I just imported another large excel file and their was a difference in format. The larger file we already have has the address, state and zip code in separate columns each spaced two apart like so:</p>
  
  <p><img src="https://i.stack.imgur.com/NdLII.png" alt="enter image description here"></p>
  
  <p>I didn't make this spreadsheet or else I wouldn't have put the columns like that, but thats how the lady that works with it likes it.</p>
  
  <p>The problem is that the sheet I imported has the city, state, and zip info all in the same cell like this:</p>
  
  <blockquote>
    <p>Trollville, NY 12345</p>
  </blockquote>
  
  <p>I have already over the states since 99% of the new ones were all the same state which a quick find and replace all worked. I'm now left with this</p>
  
  <blockquote>
    <p>Trollville 12345</p>
  </blockquote>
  
  <p>I want to move that zip code four columns to the right into the proper cell. I wrote a basic regex but don't know much about excel-vba since I haven't used it in years, but this is what I've come up with. I just don't know how to tell vba to print output the matches (which I made into an array) into the appropriate column. This is what I have so far:</p>
  
  <pre><code>Function findZipCode(zipCode)
       Dim regEx As New VBScript_RegExp_55.RegExp
       Dim matches, s
       regEx.Pattern = "\s\d{5}\W"
       regEx.Global = True
       s = ""
       If regEx.Test(zipCode) Then
           Set matches = regEx.Execute(zipCode)
           For Each Match In matches
               s = s &amp; Match.Value
  
           Next
           findZipCode = s
       Else
           findZipCode = ""
       End If
  End Function
  </code></pre>
  
  <p>What do I need to add? I'm open to alternative methods too if there is an easier way to do this. </p>
  
  <p>Thanks in advance for the advice</p>