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

wrote in message
oups.com...
Hi, I have the following UDF and I wish to put this in column 3 of a
sheet for a 100 rows, but I can't get it to work. Any ideas?

Function quizzer(txt As String)
With CreateObject("VBScript.RegExp")
.Pattern = "([a-zA-Z]+\.)?\d+"
quizzer = .Execute(txt)(0)
End With
End Function

I have tried the following, but I can not get the cell reference to
change.

Sub test()

For x = 1 To 100
r = Range("c65536").End(xlUp).Row + 1
frml = "A" & r
Cells(r, 3).Formula = "=quizzer(frml)"
r = r + 1
Next x
End Sub