What do you mean you "are having trouble with the pattern"? What are you
trying to do? What do you want to see happen? What do you want Excel to do
for you? HTH Otto
wrote in message
ups.com...
I have data which is in this format:

J 123 K

The J is optional, could be upper or lower case, and could be a J or
an X or a Z.

It is always followed by a space. The three digits. Then a space.
Then any alphabetic character upper or lower case, but not optional,
the character must be there.

I am having trouble with the pattern, could someone show me how to set
it up?

tia
bob


Const sPattern As String = "([JjXxZz]\s)?\d{1,3}(?=\ )(\D\s)"

Set oRegex = New RegExp
oRegex.Pattern = sPattern
oRegex.Global = True

'check all of the rows
With ActiveSheet
S = .Cells(lR, iCWD).Value2
If oRegex.Test(S) = True Then
Set colmatches = oRegex.Execute(S)
strData = colmatches(0)
'convert the dewey to numeric if it does not
'have a leading alpha
If (bStringsT_IsLongInteger(strData) = True) Then
.Cells(lR, iCO).Value2 =
iStringsT_StringToIntegerNumber(strData)
Else
.Cells(lR, iCO).Value2 = colmatches(0)
End If
End If
End With