Query to Remove Spaces, Characters from a list

[JGARDNER-AIT]

Hello,
I have a long list of part numbers in a field called MPN. I'm trying to remove all characters and spaces. I want to only leave numeric and alpha numeric text only.

Anyones help would be greatly appreciated.
Thanks - Josh

 

[pavin]

Hi, Use the following code. The code tests for the special charecters in the activecell and deletes it. However you can check this in a range by adding one more loop. Hope this helps.

Sub test()
TxtLengh = Len(ActiveCell.Value)
    For i = 1 To TxtLengh
    If i > Len(ActiveCell.Value) Then Exit Sub
        MyVal = Mid(ActiveCell.Value, i, 1)
      If (Asc(MyVal) > 47 And Asc(MyVal) < 58) Or _
         (Asc(MyVal) > 64 And Asc(MyVal) < 91) Or _
         (Asc(MyVal) > 96 And Asc(MyVal) < 123) Then
         Else
         LResult = Replace(ActiveCell.Value, Chr(Asc(MyVal)), "")
            ActiveCell.Value = LResult
            i = i - 1
      End If
    Next i
End Sub

 

[pavin]

Use the function either: paste the following code in a module and use the function. Ex:, If you are checking a string in cell A1 then type =SplChrDelete(A1) in cell A2. Hope this helps.

Function SplChrDelete(Target As String)
TxtLengh = Len(Target)
    For i = 1 To TxtLengh
    If i > Len(Target) Then Exit Function
        MyVal = Mid(Target, i, 1)
      If (Asc(MyVal) > 47 And Asc(MyVal) < 58) Or _
         (Asc(MyVal) > 64 And Asc(MyVal) < 91) Or _
         (Asc(MyVal) > 96 And Asc(MyVal) < 123) Then
         Else
         LResult = Replace(Target, Chr(Asc(MyVal)), "")
            Target = LResult
            SplChrDelete = LResult
            i = i - 1
      End If
    Next i
End Function

 

[pavin]

I am sorry JOSH, I thought you are looking for help in Excel.

But you can use the Function code in Access aswell.

 

[JGARDNER-AIT]

I am sorry JOSH, I thought you are looking for help in Excel.

But you can use the Function code in Access aswell.

Pavin... Thank you! Works well so far.
Thanks - Josh