CODEWITHSUNDEEP
vba
natwar lal
natwar lal

Reputation: 331

Function which Removes Only Non-ASCII characters in a column in access table

I have a access table and i am writing a vba code to remove non-ascii characters from the table, i have tried using below two functions 

Public Function removeall(stringData As String) As String
    Dim letter As Integer
    Dim final As String
    Dim i As Integer

    For i = 1 To Len(stringData) 'loop thru each char in stringData

        letter = Asc(Mid(stringData, i, 1)) 'find the char and assign asc value

        Select Case letter 'Determine what type of char it is
            Case Is < 91 And letter > 64 'is an upper case char
                final = final & Chr(letter)
            Case Is < 123 And letter > 96 'is an lower case char
                final = final & Chr(letter)
            Case Is = 32  'is a space
                final = final & Chr(letter)
    End Select

    Next i
    removeall = final

    End Function

And also tried using below function 

Public Function Clean(InString As String) As String
'-- Returns only printable characters from InString
   Dim x As Integer
   For x = 1 To Len(InString)
      If Asc(Mid(InString, x, 1)) > 31 And Asc(Mid(InString, x, 1)) < 127 Then
         Clean = Clean & Mid(InString, x, 1)
      End If
   Next x

End Function

But the problem is : In removeall function it removes everything including # and space characters.. And In Clean function also removes special characters as well.

I need a correct function which retains key board characters and removes all other characters

Examples of strings in tables are :

1) "ATTACHMENT FEEDING TUBE FITS 5-18 ºFR# "

2) "CATHETER FOLEY 3WAY SILI ELAST 20FR 30ML LATEXº"

Any help would be greatly appreciated

Output should be like

1) "ATTACHMENT FEEDING TUBE FITS 5-18 FR"

2) "CATHETER FOLEY 3WAY SILI ELAST 20FR 30ML LATEX"

Upvotes: 5

Views: 5475

Answers (2)

Erik A
Erik A

Reputation: 32682

Alternate approach that preserves ALL ASCII characters, without working with a whitelist, in a single function:

Public Function RemoveNonASCII(str As String) As String
    Dim i As Integer
    For i = 1 To Len(str)
        If AscW(Mid(str, i, 1)) < 127 Then 'It's an ASCII character
            RemoveNonASCII = RemoveNonASCII & Mid(str, i, 1) 'Append it
        End If
    Next i
End Function

Upvotes: 4

Spangen
Spangen

Reputation: 4740

One approach would be to use a whitelist of accepted characters. e.g.

' You can set up your domain specific list:
Const Whitelist = "1234567890" & _
                  "qwertyuiopasdfghjklzxcvbnm" & _
                  "QWERTYUIOPASDFGHJKLZXCVBNM" & _
                  " `~!@#$%^&*()_-=+[]{};:""'|\<>?/ –"

Public Sub test()

    Debug.Print Clean("ATTACHMENT FEEDING TUBE FITS 5-18 ºFR#")
    Debug.Print Clean("CATHETER FOLEY 3WAY SILI ELAST 20FR 30ML LATEXº")

End Sub

Public Function isAllowed(char As String) As Boolean

    isAllowed = InStr(1, Whitelist, char, vbBinaryCompare) > 0

End Function


Public Function Clean(dirty As String) As String
'-- Returns only printable characters from dirty
   Dim x As Integer
   Dim c As String
   For x = 1 To Len(dirty)
      c = Mid(dirty, x, 1)
      If isAllowed(c) Then
         Clean = Clean & c
      End If
   Next x

End Function

Upvotes: 4

Related Questions