Thanks Ron,

Works like a charm. I am very grateful.

Happy coding,

Bhupinder.

"Ron Rosenfeld" wrote:

On Wed, 26 Apr 2006 04:56:02 -0700, Bhupinder Rayat
wrote:

Thanks Ron,

but now it doesnt like Set mc = re.Execute(rSearch.Text), error message says
"Object variable or With block variable not set".

Any ideas?

Thanks again,

Bhupinder


Well, my first suggestion would be to use Longre's morefunc add-in and the
Regex formulas I posted previously. Morefunc can be easily distributed with a
workbook.

If you must use a VBA solution, then I would use this one, which I wrote myself
so I know it works, and use =REMID(A1,"\b\w{12}(1|h|c)\b")

(same pattern but different formula).

Again, if you have multiple codes within the string, there is an optional third
argument to parse out the instance.

And also, you'll have to set the reference to vbscript as I previously wrote.

===============================
Option Explicit
Function REMid(str As String, Pattern As String, _
Optional Index As Variant = 1, _
Optional CaseSensitive As Boolean = True) _
As Variant 'Variant as value may be string or array

Dim objRegExp As RegExp
Dim objMatch As Match
Dim colMatches As MatchCollection

Dim i As Long 'counter
Dim t() As String 'container for array results

' Create a regular expression object.
Set objRegExp = New RegExp

'Set the pattern by using the Pattern property.
objRegExp.Pattern = Pattern

' Set Case Insensitivity.
objRegExp.IgnoreCase = Not CaseSensitive

'Set global applicability.
objRegExp.Global = True

'Test whether the String can be compared.
If (objRegExp.Test(str) = True) Then

'Get the matches.
Set colMatches = objRegExp.Execute(str) ' Execute search.

On Error Resume Next 'return null string if a colmatch index is non-existent
If IsArray(Index) Then
ReDim t(1 To UBound(Index))
For i = 1 To UBound(Index)
t(i) = colMatches(Index(i) - 1)
Next i
REMid = t()
Else
REMid = CStr(colMatches(Index - 1))
If IsEmpty(REMid) Then REMid = ""
End If
On Error GoTo 0 'reset error handler
Else
REMid = ""
End If
End Function
===============================
--ron