Otto: It is obvious from the code he is working with C++. The patterns he is
working with goes back to the development of UNIX at Bell Labs (and probably
earlier than that). The syntax that Bob is using describes a custom language
that is used to describe strings consisting of words and characters.

Bell Labs did lots of research on Pattern Recognition like this to develope
efficient methods for searching for name in a Phone Book. Computers were
very slow and memory was very expensive in the 1970's. Bell Labs were
trying to save money by by find efffiecent methods for storing their phone
books electronically and finding name quickly using computers so operators
didn't have to manually look up people names.

That was one of the main reasons UNIX was developed. Bell labs had lots of
computer systems that couldn't talk to each other (phonebook, billing,
switching equipment) and wanted to develope one computer language that could
be used by all there computers.

"Otto Moehrbach" wrote:

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