Removing numbers (or non alphabetical numbers from a field)

[Gaijintendo]

I am used to having regular expressions in SQL, but I am using access 2000 at
work, and need to strip out all numbers (and ideally non-alphabetical
characters) from a column.
I am aware of
Replace(Column,'1','')

Is there a way to list the targets needing replaced?

 

[Allen Browne]

Here's a function that will strip out any characters you pass in the 2nd
argument:

Function Strip(varPhrase As Variant, strBadChars As String) As Variant
' Purpose: remove any of the characters in strBadChars from varPhrase
Dim sPhrase As String
Dim i As Integer

If IsNull(varPhrase) Or IsEmpty(varPhrase) Or Len(strBadChars) = 0 Then
Strip = varPhrase
Else
sPhrase = varPhrase
i = 1
Do Until i > Len(sPhrase)
Do Until InStr(strBadChars, Mid$(sPhrase, i, 1)) = 0 Or i >
Len(sPhrase)
sPhrase = Left$(sPhrase, i - 1) & Mid$(sPhrase, i + 1)
Loop
i = i + 1
Loop
Strip = sPhrase
End If
End Function

 

[Gaijintendo]

I am fairly naiive, when it comes to functions.

Am I correct in saying, I create a new "Module", using this code, and save it?
If so, are there any more steps before I can call this function, because I
am not having any success...

 

[Allen Browne]

That's right: create a new standard module (not a class module.)
Use the Insert menu in the code window.

Paste the function in there. Save with a name such as Module1 (not Strip or
any other function name.) Then choose Compile on the Debug menu to ensure
that VBA understands it.

Test it by opening the Immediate Window (Ctrl+G), and entering:
? Strip ("Wh5a4t6", "456")

Once you have that working, you should be able to use it in a query or other
contexts.

 

[Gaijintendo]

That is not only a really useful explanation, but it is really enabling -
hopefully I will be asking a few less questions now!


Thanks!!!

 

[Allen Browne]

That is not only a really useful explanation, but it is really enabling

Now that's really satisifying!

That's exactly what we strive to achieve here.
Thanks.