Surrounded by letters, not intermixed?

Create a User Defined function (in a module).

Function ValNumber(strString As String) As Long
On Error GoTo Err_Handler
Dim intX As Integer
Dim intY As Integer
intY = Asc(strString)
Do While intY < 48 Or intY > 57
  intX = intX + 1
     If intX = Len(strString) Then Exit Do
        intY = Asc(Mid(strString, intX, 1))
Loop
If intX = 0 Then intX = 1

ValNumber = Val(Mid(strString, intX))

Exit_ValNumber:
  Exit Function
Err_Handler:
  MsgBox "Error: " &  Err.Number & "  " & Err.Description
Resume Exit_ValNumber

End Function

You can call it from a query using:
NewColumn:ValNumber([FieldNaem])

or directrly in an unbound control on a form or report:
=ValNumber([FieldName])