vba - 仅删除访问表中列中的非 ASCII 字符的函数

Question

我有一个访问表，我正在写一个 vba删除代码 non-ascii表中的字符，我尝试使用以下两个函数

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

并尝试使用以下功能

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

但问题是:在 removeall function它会删除所有内容，包括 #和 space字符.. 和在 Clean function也删除 special characters以及。

我需要一个正确的函数来保留键盘字符并删除所有其他字符

表格中的字符串示例如下:

1) “附件进料管适合 5-18 ºFR#”

2) “导管 FOLEY 3WAY SILI ELAST 20FR 30ML LATEXº”

任何帮助将不胜感激

输出应该像

1) “附件供料管适合 5-18 FR”

2) “导管 FOLEY 3WAY SILI ELAST 20FR 30ML latex ”

Answer 1

一种方法是使用接受字符的白名单。例如

' 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