Stripping everything but numbers in Access

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




    PSA #3411     


    P.O. 411894-G 


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







    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! 

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

  • 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.

  • Strip them using what? SQL or VB?

  • 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)


    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
        ParseDbl = CDbl(result)
    End Function


    SELECT ParseDbl(Field1) AS Expr1
    FROM Table1;

Log in to reply

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.