I have a table with a text field (named Description), which may or may not contain an "ID Number", which is of the form [CHSV]#[A-Z][A-Z][A-Z]## - NB this is not necessarily at the beginning or the end, and will vary between records. For e.g.

"something C4CLE01"
"something H5STT02 Something else"

What I would like to do in a query is to extract just the ID number part and put that in a new field e.g.

"something C4CLE01" => "C4CLE01"
"something H5STT02 Something else" => "H5STT02"

So far I have considering LIKE, which is no good because it doesnt tell you where in the string the regular expression is, and INSTR doesnt work with regular expressions AFAIK.

Added bonus difficulty - I use the database as an external source for an Excel pivot table. I created a function (which I named JOBID) in VBA to do the above job easily enough i.e. I added a field "JOBID([Description])", but when I try to use the data in said pivot table, it says "Undefined function JOBID in expression". I have tried copying the function to the Excel workbook, but that doesn't work.

Im using Office 2003 if that makes any difference. Thanks in advance.

If the number is always preceded by the first space in the field and is always 7 characters then the following expression should work

Mid([Description], Instr([Description]," ")+1,7)

Unfortunately not that easy; it could be anywhere; beginning, end after 1 space, after 100 spaces. It will always be exactly 7 characters, as alluded to by the regular expression. Thanks though.

Then you're stuck. Pattern matching like this is not supported in Access. You might be able to code this but I wouldn't know where to start.

Function fParser(sPassed As String) As String
Dim i As Integer
Dim vElement As Variant

fParser = "N/A"

For Each vElement In Split(sPassed)
If Len(vElement) = 7 Then
Select Case Asc(Left(vElement, 1))
Case 67, 72, 83, 86 'C,H,S,V
For i = 2 To 7
Select Case i
Case 2, 6, 7
If Not IsNumeric(Mid(vElement, i, 1)) Then Exit For
Case 3 To 5
If Asc(Mid(vElement, i, 1)) > 90 Or _
Asc(Mid(vElement, i, 1)) < 65 Then Exit For
End Select
If i = 7 Then fParser = vElement 'set done
Next i
If fParser = vElement Then Exit For 'out if done
End Select
End If
Next vElement

End Function

Thanks Doug, but I can write such a function myself. The problem is that when I point my Excel pivot table to my Access database as an External Data Source, then the function that I write is not picked up by Excel and I get an error message (see original post). I guess I need to be able to do what I want to do in standard Access functions i.e. not using VBA, but I don't know if this is possible.