Stripping Non-Numbers or Letters from a Text String 2

[MacroScope]

I have a large DB with about 20 users, Access 2007 SQL, for an insurance company. One of the fields is for policy number. Users input the policy number, but they don't always exactly match the company formatting. Some companies may have something like this: THM 1653897, another may have 1-06987-42. I'm trying to match the policy numbers that come back from the insurance company in their spreadsheet with numbers in our system.

Some people may vary the policy numbers used as examples when inputting them. The first could be THM-1653897, and the second might be 1 06987-42 or 106987-42, etc. You get the picture.

What I'd like to do is to update the user input string and strip away all extra characters, spaces, etc., leaving only letters and numbers. The result I'm seeking in the first example would be THM1653897, the second would be 10698742. I can then reformat it using the same parameters the insurance company does and then match them.

Can someone offer a means of stripping away any non-letter or number and space. I have no problem with the reformatting. It's the stripping that I'm not sure how to accomplish.

All help would be appreciated.

 

[PHV]

You may use an UDF like this:

Public Function getAlphaNumOnly(strValue)
If IsNull(strValue) Then Exit Function
Dim i As Long, x As String, strReturn As String
For i = 1 To Len(strValue)
    x = Mid(strValue, i, 1)
    If x Like "[0-9A-Z]" Then
        strReturn = strReturn & x
    End If
Next
getAlphaNumOnly = strReturn
End Function

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MacroScope]

Thanks. I'm not at the work computer and can't try it right now but I will on Tuesday.

I appreciate the input. I'll let you know.

 

[MacroScope]

Thanks, PHV. I played with this at home and it works great. I really appreciate the help. How do I mark the thread as solved?