remove all non numeric characters

[johnawebbhsd]

I am trying to write an append table query to remove any non numeric character. For example my original table has some like "MSGLN 123 45-6789 #99 For Home" I would like the query to change that to 12345678999 I have been stuggling for this and searched the board for something similar. Any ideas?

 

[Dugantrain]

John,
This would call for an Update Query and a VBA function. First the function:

Public Function fGetNums(StrText As String) As String
Dim NumLen As Long
Dim strFinalText As String
Dim i As Long
NumLen = Len(StrText)  'Get the length of the string that you passed in
strFinalText = "" 'This string variable starts out empty
'Now your big loop which loops through each character in the string,
'determines whether or not the character is a number, and then either adds
'or rejects that character based on whether or not it passes the number validation
For i = 1 To NumLen 'Loop starts at 1 and goes until the max string length
    If IsNumeric(Mid(StrText, i, 1)) Then 'Test if is numeric or not
        strFinalText = strFinalText & Mid(StrText, i, 1) 'Numeric; add character
    Else
        strFinalText = strFinalText 'Not Numeric; reject character
    End If
Next i  'Go to next character
fGetNums = strFinalText  'Function result of strFinalText after loop 
End Function

Now the Update Query:
Open up a query and make it an Update Query. Select the field which you want Updated. In its "Update To" field, you would write:

GetNums([Name_of_Your_Field])

 

[Russell Hauf]

Either of these should work as well - just wanted to show that there's more than one way to skin a cat.:

Option Compare Database
Option Explicit

Function NumbersOnly(ByVal strText As String) As String
    Dim intI As Integer
    Dim intLen As Integer
    
    intLen = Len(strText)
    
    For intI = intLen To 1 Step -1
        If Not IsNumeric(Mid(strText, intI, 1)) Then
            strText = Left(strText, intI - 1) & Mid(strText, intI + 1)
        End If
    Next intI
    
    NumbersOnly = strText
End Function

or

Function NumbersOnly2(strText As String) As String
    Dim intI As Integer
    Dim strNums As String
    
    For intI = 1 To Len(strText)
        If IsNumeric(Mid(strText, intI, 1)) Then
            strNums = strNums & Mid(strText, intI, 1)
        End If
    Next intI
    
    NumbersOnly2 = strNums
End Function

Both of these and the previous poster's function should give the same result - happy trails!

-rh

 

[Russell Hauf]

Sorry - my second function is essentially the same as the previous post. I had my window a little narrow and everything was wrapping.

Dugantrain, the:

    Else
        strFinalText = strFinalText 'Not Numeric; reject character

part is unnecessary.

 

[johnawebbhsd]

Thanks for the help.. this works for me.... It is taking while, but i think that is because I have about 900000 records in the table.. The only change I made was to the piece that goes in the Update To Field I had to put an 'f' in front of GetNum..... All seems to be working great though... Thanks,

 

[Dugantrain]

Oh yeah, sorry about the missing "f". I originally wrote and tested the entire function without the leading "f" and then decied to edit the post later so that it would follow a more standard naming convention.