Selectively Extract Alpha-Numeric Data From A Field

[jturner]

Your assistance will be greatly appreciated:

In a field named "Comments" I need to extract a error code that begins with
E and a four digit number. (i.e E2301 and E1003)
I am trying to use the instr command (Error:
Mid([Comments],InStr([Comments],"E"),5)) but I can't figure how I can add
the various digits that may appear. The valid codes will appear from E0000
to E9999.
As you can see in the first line will produce a result ERROR and not E2301
as I would like.

COMMENTS
PC#18644728379 VOD ERROR E2301 CCI STATED PREVIOUS TECH WRITE
C# (864)313-6362 VOD E1003 CANT GET INTO ME

 

[Marshall Barton]

Your assistance will be greatly appreciated:

In a field named "Comments" I need to extract a error code that begins with
E and a four digit number. (i.e E2301 and E1003)
I am trying to use the instr command (Error:
Mid([Comments],InStr([Comments],"E"),5)) but I can't figure how I can add
the various digits that may appear. The valid codes will appear from E0000
to E9999.
As you can see in the first line will produce a result ERROR and not E2301
as I would like.

COMMENTS
PC#18644728379 VOD ERROR E2301 CCI STATED PREVIOUS TECH WRITE
C# (864)313-6362 VOD E1003 CANT GET INTO ME

try this kind of logic:

pos = InStr(comments, "E")
Do Until pos = 0
If Mid(comments, pos +1) Like "####" Then
strEnum = Mid(comments, pos, 5)
Exit Do
End If
pos = InStr(pos, comments, "E")
Loop