You could use the VBA replace function (Access 2000 or later)

rgxExtract(Replace([The field],Chr(13) & Chr(10)," ") ,"\w+\s\d\w\d{4}",0)

In a query, you could do something like

Field: Stripped: rgxExtract(Replace([TheField],Chr(13) & Chr(10)," ")
,"\w+\s\d\w\d{4}",0)
Criteria: Not null or <> ""

I would probably use an additional column and apply criteria to it on the
theory that SQL engine is going to weed things down for me before I do the
rgxExtract.  You might try it both ways and see if there is any benefit to
using the like criteria versus using criteria on just the stripped result.

Field: TheField
Criteria: Like "*#[a-z]####*"

Of course, one benefit of using criteria on only the "Stripped" result is
that you don't have to figure out a like criteria that matches up with what
you want to extract.

"EllenM" <EllenM@discussions.microsoft.com> wrote in message
<3166CD52-1FCA-4612-86BF-642CD83CB498@microsoft.com>:

I don't understand your pattern. You say you want only the "3b3333",
but the pattern "\w+\s\d\w\d{4}" should give you a bit more than that.

n* - zero or more new line
w+ - one or more occurences of [a-zA-Z_0-9]
s  - whitespace

which occurs before "your pattern".

d  - is numeric
w  - again, is [a-zA-Z_0-9] - are you sure about that, or should you
rather use for instance [a-zA-Z]?

I would guess perhaps "\d[a-zA-Z]\d{4}", perhaps, to ensure it doesn't
exist within other words, you could test for word boundaries,
"\b\d[a-zA-Z]\d{4}\b"

Here's an attempt at a function. I'd suggest a module level variable
for the regexp object, placed in the declaration section

Private mre                 As Object

' then the function

Public Function rvsGetText(ByVal TheString As Variant) As String

   Dim mc                  As Object
   Const MyPattern         As String = "\d[a-zA-Z]\d{4}"

   If Not IsNull(TheString) Then
       If mre Is Nothing Then
           Set mre = CreateObject("vbscript.regexp")
       End If
       With mre
           .MultiLine = True
           .Pattern = MyPattern
           Set mc = .Execute(TheString)
           If mc.Count > 0 Then
               rvsGetText = mc(0)
           End If
           Set mc = Nothing
       End With

   End If

End Function

In the query, call it like this

NewField: rvsGetText([YourField])

or in SQL

SELECT rvsGetText([YourField]) as NewField, ...

There's no errorhandling, though, and it will return ZLS if no match.