How to removing non Alpha-numeric characters

bleeet

Board Regular
Joined
May 11, 2009
Messages
208
Office Version
  1. 2013
Platform
  1. Windows
Hi guys

I am new to access and I have made a data base but one of the columns has some non Alpha-numeric characters in each row that I want to remove. The fastest way to do this is to use VBA I guess. Does anyone happen to know how I can do this?


thanks
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
63,315
Office Version
  1. 365
Platform
  1. Windows
I was able to adapt the VBA User Defined Function here: Access World Forums - View Single Post - How to get rid of non - alpha-numeric characters? to do that. Here is the code:
Code:
Public Function AlphaNumOnly(inputString As Variant) As String


    Dim i As Integer
    Dim myString As String


    If Len(inputString) > 0 Then
        For i = 1 To Len(inputString)
            If Mid(inputString, i, 1) Like "[A-Z,0-9]" Then
                myString = myString & Mid(inputString, i, 1)
            End If
        Next i
    End If


     AlphaNumOnly = myString


End Function
So, if you place this code in a Standard VBA Module in your database, you will be able to use this like any other Access function in a Query calculated field, VBA, etc., i.e.

MyNewString: AlphaNumOnly([TextField])
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,301
Messages
5,836,493
Members
430,436
Latest member
fefenouil

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top