VBA regexp pattern combining match and not matching a pattern
<p>Trying to extract valid phone numbers from a string with no visible delimiters between different data types.  In effect, the data around the potential phone number is random and irrelevant.</p>

<p><br>
<strong>What should be matched</strong>. I am trying to match either one of the following: </p>

<ul>
<li><code>[random garbage][optional '1'][optional '(']###[optional')'[[random or no white space]###-####[random garbage]</code> or </li>
<li><code>[random garbage]###[optional '-']###,[optional '-']####[random garbage]</code></li>
</ul>

<p>Only the 1st phone number is to be used, so I used <code>Global = False</code> in the code below. I could make it even more robust, but I've examined the data and this should be enough.</p>

<p><br>
<strong>Working pattern</strong>.Here's a code snippet from a function (it returns the matching phone number) that contains the pattern that worked.</p>



<pre class="lang-vb prettyprint-override"><code>With regex
    .Global = False
    .Pattern = "1?(\(?\d{3}\)?\(s+)?|\d{3}-?)\d{3}-?\d{4}"
'this works but does detect an extension as explained above
End With
</code></pre>

<p><br>
<strong>What should not be matched</strong>. I realized that I also need to search for an extension next to the phone number (i.e. <code>[phone number][white space]x#</code>) and <strong>if that exists, to treat the phone number as invalid</strong> (<code>.test</code> should evaluate to false).</p>

<p><br>
<strong>Failed attemps</strong>. They all failed (even valid phone numbers had <code>.test</code> evaluate to false):</p>

<pre class="lang-vb prettyprint-override"><code>.Pattern = "1?(\(?\d{3}\)?\(s+)?|\d{3}-?)\d{3}-?\d{4}^(\s?x\d)"
'detect not([optional single white space]x#), added "^(\s?x\d)"
'or
.Pattern = "1?(\(?\d{3}\)?\(s+)?|\d{3}-?)\d{3}-?\d{4}^((\s+?)[x]\d)"
'detect not([optional multiple white space]x#), added "^((\s+?)[x]\d)"
</code></pre>

<p>Not sure how to combine positive match tests and negative (not) match tests in the same pattern. </p>

<p><br>
<strong>Work-arounds I've tried</strong>. When I couldn't get it to work, I tried the following <code>Like</code> patterns (using VBA 'Like', prior to calling the function that utilized Regexp) and that also failed (all evaluated to false even with test strings that contained examples such as <code>"...1x2"</code> or <code>"5 x33"</code> or <code>"...7  x444"</code>; with patterns like <code>"*#x#*"</code>, <code>"*#{ x}#*"</code>, <code>""*#{  x}#*"</code>.</p>

<p>Here is the code snippet to test the Like function:</p>

<pre class="lang-vb prettyprint-override"><code>If Not (OrigNum Like "*#x#" Or OrigNum Like "*#[ x}#" Or OrigNum Like "*#[  x]#*") Then
    Debug.Print "No #x# in string"
    OrigNum = ExtractPhoneNumber(OrigNum)
Else
    Debug.Print "#x# in string"
End If
</code></pre>

<p>Every string evaluated ended up causing <code>"No x# in string"</code> to be displayed (evaluated to false), even when the string contained the above examples, which should have evaluated to true and <code>"#x# in string"</code> being displayed.</p>

<p>Dazed and confused for so long it's...OK, enough of the Led Zepp reference :-)</p>