TC,

I think brute force would be more successful.  You'd have to build a
function to inspect each character in the string.  Start with the leftmost
and discard it and test again if not numeric.  In case there's a customer
name with a number in it, you should probably make sure that if the character
is numeric it is followed by five more and a space.

Use things like:
strIn = "WIDGETS CO & SONS 262378 897-SMITH, JOHN "
strTest = left(strIn,1)
IsNumeric(strTest)
if not, strIn = right(strIn,len(strIn)-1) and loop

Good luck.

George
[someone who wouldn't know code elegance if bitten by it!]

Hi Tom,

InStr() doesn't do pattern matching, only literal matches, and Like
doesn't tell you where it finds a match.

I've built a function that uses the VBScript regular expression object
for this sort of parsing; you'll find it at
http://www.j.nurick.dial.pipex.com/Code/vbRegex/rgxExtract.htm

A pattern like this should do the job (everything up to a 6-digit
string:

    (^.*)\s\d{6}

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

No.  Like is the only built-in operator or function that
uses wildcards.

You can easily write your own function that does what you
want though.  Try something along the lines of this untested
air code:

Public Function GetCode(StrCode As String,
                                                Pat As String
                                              ) As String
Dim k As Long

For k = 1 To Len(StrCode) - Len(Pat)
    If Mid(StrCode, k) Like Pat Then
        GetCode = Mid(StrCode, k, Len(Pat))
    End If
Next k
End Function

Well, that untested function certainly won't work :-(

Here's another (quick test) version that mimics InStr much
more closely:

Public Function InStrPat(Start As Variant, _
                             String1 As Variant, _
                             Optional String2 As Variant _
                          ) As Variant
Dim lngStart As Long
Dim strText As String
Dim strPat As String
Dim Lg As Long, K As Long

  InStrPat = Null
  If IsMissing(String2) Then
     If IsNull(Start) Or IsNull(String1) Then Exit Function
     lngStart = 1
     strText = Start
     strPat = String1
  Else
     If IsNull(Start) Or IsNull(String1) Or IsNull(String2)
Then Exit Function
     lngStart = Start
     strText = String1
     strPat = String2
  End If
 
  Lg = Len(strPat)
  InStrPat = 0
  For K = lngStart To Len(strText) - Lg
     If Mid(strText, K, Lg) Like strPat Then
        InStrPat = K
     End If
  Next K
End Function