Is more concise but very slow.
Faster.
<table width="100%" border="1" bgcolor="White" style="filter
rogid:DXImageTransform.Microsoft.Gradient(endColorstr='#C0CFE2', startColorstr='#FFFFFF', gradientType='0');"><tr><TD><font size="2" face=Courier New> <font color="#0000A0">Private</font> ovr <font color="#0000A0">As</font> <font color="#0000A0">Object</font>
<font color="#0000A0">Sub</font> Example()
<font color="#0000A0">Dim</font> MyPurchaseOrderRange <font color="#0000A0">As</font> Range, Ec <font color="#0000A0">As</font> Range
<font color="#0000A0">Set</font> MyPurchaseOrderRange = Application.InputBox( _
"Select the range containing your Purchase Order Id's", , "$A$1:$A$3437", , , , , 8)
<font color="#0000A0">If</font> MyPurchaseOrderRange <font color="#0000A0">Is</font> <font color="#0000A0">Nothing</font> <font color="#0000A0">Then</font> <font color="#0000A0">Exit</font> <font color="#0000A0">Sub</font>
<font color="#0000A0">Set</font> ovr = CreateObject("VBSCRIPT.REGEXP")
<font color="#0000A0">For</font> <font color="#0000A0">Each</font> Ec <font color="#0000A0">In</font> MyPurchaseOrderRange
Ec = CleanPO(Ec.Text)
<font color="#0000A0">Next</font>
<font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
<font color="#0000A0">Function</font> CleanPO(DirtyPO <font color="#0000A0">As</font> String) <font color="#0000A0">As</font> <font color="#0000A0">String</font>
<font color="#0000A0">With</font> ovr
.Pattern = ".*?(\d{7}).*"
<font color="#0000A0">If</font> .test(DirtyPO) <font color="#0000A0">Then</font>
CleanPO = .Replace(DirtyPO, "$1")
<font color="#0000A0">Else</font>
CleanPO = "Not found PO number in :" & DirtyPO
<font color="#0000A0">End</font> <font color="#0000A0">If</font>
<font color="#0000A0">End</font> <font color="#0000A0">With</font>
<font color="#0000A0">End</font> <font color="#0000A0">Function</font>
</FONT></td></tr></table><button onclick='document.all("9162006175523275").value=document.all("9162006175523275").value.replace(/<br \/>\s\s/g,"");document.all("9162006175523275").value=document.all("9162006175523275").value.replace(/<br \/>/g,"");window.clipboardData.setData("Text",document.all("9162006175523275").value);'>Copy to Clipboard</BUTTON><textarea style="position:absolute;visibility:hidden" name="9162006175523275" wrap="virtual">
Private ovr As Object
Sub Example()
Dim MyPurchaseOrderRange As Range, Ec As Range
Set MyPurchaseOrderRange = Application.InputBox( _
"Select the range containing your Purchase Order Id's", , "$A$1:$A$3437", , , , , 8)
If MyPurchaseOrderRange Is Nothing Then Exit Sub
Set ovr = CreateObject("VBSCRIPT.REGEXP")
For Each Ec In MyPurchaseOrderRange
Ec = CleanPO(Ec.Text)
Next
End Sub
Function CleanPO(DirtyPO As String) As String
With ovr
.Pattern = ".*?(\d{7}).*"
If .test(DirtyPO) Then
CleanPO = .Replace(DirtyPO, "$1")
Else
CleanPO = "Not found PO number in :" & DirtyPO
End If
End With
End Function
</textarea>
Is very fast.
Set a reference to Microsoft VBScript Regular Expressions 5.5
<table width="100%" border="1" bgcolor="White" style="filter
rogid:DXImageTransform.Microsoft.Gradient(endColorstr='#C0CFE2', startColorstr='#FFFFFF', gradientType='0');"><tr><TD><font size="2" face=Courier New> <font color="#0000A0">Sub</font> Example()
<font color="#0000A0">Dim</font> MyPurchaseOrderRange <font color="#0000A0">As</font> Range, Ec <font color="#0000A0">As</font> Range
<font color="#0000A0">Dim</font> VbRegEx <font color="#0000A0">As</font> VBScript_RegExp_55.RegExp
<font color="#0000A0">Set</font> MyPurchaseOrderRange = Application.InputBox( _
"Select the range containing your Purchase Order Id's", , "$A$1:$A$3437", , , , , 8)
<font color="#0000A0">If</font> MyPurchaseOrderRange <font color="#0000A0">Is</font> <font color="#0000A0">Nothing</font> <font color="#0000A0">Then</font> <font color="#0000A0">Exit</font> <font color="#0000A0">Sub</font>
<font color="#0000A0">Set</font> VbRegEx = <font color="#0000A0">New</font> VBScript_RegExp_55.RegExp
<font color="#0000A0">For</font> <font color="#0000A0">Each</font> Ec <font color="#0000A0">In</font> MyPurchaseOrderRange
Ec = CleanPO(Ec.Text, VbRegEx)
<font color="#0000A0">Next</font>
<font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
<font color="#0000A0">Function</font> CleanPO(DirtyPO <font color="#0000A0">As</font> String, VbRegEx <font color="#0000A0">As</font> VBScript_RegExp_55.RegExp) <font color="#0000A0">As</font> <font color="#0000A0">String</font>
<font color="#0000A0">With</font> VbRegEx
.Pattern = ".*?(\d{7}).*"
<font color="#0000A0">If</font> .test(DirtyPO) <font color="#0000A0">Then</font>
CleanPO = .Replace(DirtyPO, "$1")
<font color="#0000A0">Else</font>
CleanPO = "Not found PO number in :" & DirtyPO
<font color="#0000A0">End</font> <font color="#0000A0">If</font>
<font color="#0000A0">End</font> <font color="#0000A0">With</font>
<font color="#0000A0">End</font> <font color="#0000A0">Function</font>
</FONT></td></tr></table><button onclick='document.all("916200618510243").value=document.all("916200618510243").value.replace(/<br \/>\s\s/g,"");document.all("916200618510243").value=document.all("916200618510243").value.replace(/<br \/>/g,"");window.clipboardData.setData("Text",document.all("916200618510243").value);'>Copy to Clipboard</BUTTON><textarea style="position:absolute;visibility:hidden" name="916200618510243" wrap="virtual">
Sub Example()
Dim MyPurchaseOrderRange As Range, Ec As Range
Dim VbRegEx As VBScript_RegExp_55.RegExp
Set MyPurchaseOrderRange = Application.InputBox( _
"Select the range containing your Purchase Order Id's", , "$A$1:$A$3437", , , , , 8)
If MyPurchaseOrderRange Is Nothing Then Exit Sub
Set VbRegEx = New VBScript_RegExp_55.RegExp
For Each Ec In MyPurchaseOrderRange
Ec = CleanPO(Ec.Text, VbRegEx)
Next
End Sub
Function CleanPO(DirtyPO As String, VbRegEx As VBScript_RegExp_55.RegExp) As String
With VbRegEx
.Pattern = ".*?(\d{7}).*"
If .test(DirtyPO) Then
CleanPO = .Replace(DirtyPO, "$1")
Else
CleanPO = "Not found PO number in :" & DirtyPO
End If
End With
End Function</textarea>