wrote in message
ups.com...
On 9 Mar, 12:41, "Peter T" <peter_t@discussions wrote:
This UDF worked for me for cell-ref or quoted string values -

Public goRegExp As Object ' set to nothing in say a deactivate event

Function quizzer(txt As Variant)
Dim oMatches As Object
If goRegExp Is Nothing Then
Set goRegExp = CreateObject("VBScript.RegExp")
End If
On Error GoTo errH
With goRegExp
.Pattern = "([a-zA-Z]+\.)?\d+"
Set oMatches = .Execute(txt)

If oMatches.Count Then
quizzer = oMatches(0)
Else
quizzer = "?"
End If
End With
Exit Function
errH:
quizzer = CVErr(xlErrNA)
End Function

Sub test()
s$ = "abc123"
MsgBox quizzer(s)
End Sub

That CreateObject in a 100 cells would be slow, hence the global ref to
RegExp

Regards,
Peter T
Thanks for the reply.

Public goRegExp As Object ' set to nothing in say a deactivate
event ????

What do I do with this, please remember you are speaking to an idiot.

It's probably harmless to do nothing, but to clean up the object reference
when not needed there are various events in which you could destroy it, eg

In the ThisWorkbook module -
Private Sub Workbook_Deactivate()
Set goRegExp = Nothing
End Sub

Whilst there look at the two drop downs, there are more events in the Sheet
modules.

Did the example work.

Regards,
Peter T