Stripping everything but numbers in Access

keatonmill

Hi there. I have fields in access with data that looks like this:

ECS-9986044

A101132

PSA #3411

122-1771A

P.O. 411894-G

I want to strip everything out that isn't a number, so I just have

9986044

101132

3411

1221771

411894

Any ideas?

Edit: By the way, I'm a long timer lurker, first time poster. Sorry that my first post is a silly question/demand!

Lingerance

Can you not make a string buffer and loop through your string(s) putting any numeric character into the buffer?

djork

I can't tell you how to strip them out in Access, but I just want to recommend that you may want to divide that 122-1771A field into two numbers in the end, 122, and 1771. You may have to sort by the first number or the second number and it could come back to bite you if they are concatenated.

dhromed

Strip them using what? SQL or VB?

RaspenJho

In access 2007 you could do use a custom function (in a module) to do the conversion, then call it from either SQL or VB. It is NOT going to be the fastest thing in the world, but it should do what you want...

Here is an example function: (Note that this does not support periods anywhere in the string)

Module:

Public Function ParseDbl(inp As String) As Double
    Dim i As Integer
    Dim result As String
    Dim s As String

    For i = 1 To Len(inp) Step 1
        s = Mid(inp, i, 1)
        If IsNumeric(s) Then
            result = result & s
        End If
    Next

    ParseDbl = CDbl(result)
End Function

SQL:

SELECT ParseDbl(Field1) AS Expr1
FROM Table1;