title: Excel macro to replace part of string
body: <p>I'm having trouble replacing part of a string in a range of data that includes comments.</p>
  
  <p>Where ID numbers appears, I need to replace the middle of the ID numbers with Xs (e.g. <code>423456789</code> to become <code>423xxx789</code>). The IDs only ever start with <code>4</code> or <code>5</code> and any other number should be ignored as it may be necessary for other purposes.</p>
  
  <p>Sadly, because these are comments the data is inconsistently formatted which adds a level of complexity.</p>
  
  <p>Representative data would look like the following:</p>
  
  <pre><code>523 123 123
  523123123
  ID 545 345 345 is Mr. Jones
  Primary ID 456456456 for Mrs. Brown
  Mr. Smith's Id is 567567567
  </code></pre>
  
  <p>I need the code to only replace the middle 3 digits of the ID number and leave the rest of the cell intact so that </p>
  
  <pre><code>ID 545 345 345 is Mr. Jones 
  Primary ID 456456456 for Mrs. Brown
  </code></pre>
  
  <p>Becomes (with or without spaces around the <code>X</code>s)</p>
  
  <pre><code>ID 545 xxx 345 is Mr. Jones 
  Primary ID 456xxx456 for Mrs. Brown
  </code></pre>
  
  <p>The regex I have is finding the lines with IDs successfully, and works nicely for the cells with no other text. Sadly, for the other cells it will not replace just the 3 digits that need replacing and makes a mess of data the cell.  My code below works for the first two cells above, then doesn't work so well for the remainder. Please help.</p>
  
  <pre><code>Sub FixIds()
  
  Dim regEx As New RegExp
      Dim strPattern As String: strPattern = "([4][0-9]{2})([^a-zA-Z0-9_]?[0-9]{3})([^a-zA-Z0-9_]?[0-9]{3})|([5][0-9]{2})([^a-zA-Z0-9_]?[0-9]{3})([^a-zA-Z0-9_]?[0-9]{3})"
  Dim strReplace As String: strReplace = ""
  Dim strInput As String
  Dim Myrange As Range
  Dim NewPAN As String
  Dim Aproblem As String
  Dim Masked As Long
  Dim Problems As Long
  Dim Total As Long
  
  'Set RegEx config/settings/properties
      With regEx
          .Global = True
          .MultiLine = True
          .IgnoreCase = False
          .Pattern = strPattern ' sets the regex pattern to match the pattern above
      End With
  
  Set Myrange = Selection
  
  MsgBox ("The macro will now start masking IDs identified in the selected cells only.")
  ' Start masking the IDs
      For Each cell In Myrange
          Total = Total + 1
          ' Check that the cell is long enough to possibly be an ID and isn't already masked
          Do While Len(cell.Value) &gt; 8 And Mid(cell.Value, 5, 1) &lt;&gt; "x" And cell.Value &lt;&gt; Aproblem
              If strPattern &lt;&gt; "" Then
  
                  cell.NumberFormat = "@"
                  strInput = cell.Value
                  NewPAN = Left(cell.Value, 3) &amp; "xxx" &amp; Right(cell.Value, 3)
                  strReplace = NewPAN
  
  ' Depending on the data, fix it
                  If regEx.Test(strInput) Then
                      cell.Value = NewPAN
                      Masked = Masked + 1
                  Else
                      ' Adds the cell value to a variable to allow the macro to move past the cell
                      Aproblem = cell.Value
                      Problems = Problems + 1
                      ' Once the macro is trusted not to loop forever, the message box can be removed
                      ' MsgBox ("Problem. Regex fail? Bad data = " &amp; Aproblem)
                  End If
              End If
          Loop
      Next cell
  
  ' All done
  MsgBox ("IDs are now masked" &amp; vbCr &amp; vbCr &amp; "Total cells highlighted (including blanks) = " &amp; Total &amp; vbCr &amp; "Cells masked = " &amp; Masked &amp; vbCr &amp; "Problem cells = " &amp; Problems)
  End Sub
  </code></pre>