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