VBA:如何从数据中删除不可打印的字符 [英] VBA: How to remove non-printable characters from data
本文介绍了VBA:如何从数据中删除不可打印的字符的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我需要以编程方式删除不可打印的字符,例如:
制表符-字符(9) 换行符-字符(10) 回车-字符(13) 数据链路转义字符(16)我启动了一个通用函数,该函数将从MS Access表单域的Lost_Focus事件中调用。
我想不出当字符串包含不需要的字符时如何识别。
Function RemoveNonPrintableCharacters(ByVal TextData) As String
Dim dirtyString As String
Dim cleanString As String
Dim iPosition As Integer
If IsNull(TextData) Then
Exit Function
End If
dirtyString = TextData
cleanString = ""
For iPosition = 1 To Len(dirtyString)
Select Case Asc(Mid(dirtyString, iPosition, 1))
Case 9 ' Char(9)
Case 10 ' Char(10)
Case 13 ' Char(13)
Case 16 ' Char(16)
Case Else ' Add character to clean field.
cleanString = cleanString & Mid(dirtyString, iPosition, 1)
End Select
Next
RemoveNonPrintableCharacters = cleanString
End Function
这是我在测试时使用的两个字符串:
This line, has multiple, tabs that need to be removed
This line, has multiple,
line
breaks
that
need to be removed
This line, has multiple, tabs that need to be removed
And
Also contains
multiple,
line
breaks
that
need to be removed
推荐答案
当我搜索要使用的快速功能时,这是排名靠前的谷歌结果,我有一个很好的旧谷歌,但没有一个完全解决我的问题的真正出现。
主要问题是,即使没有问题,所有这些函数也会触及原始字符串。这会让事情变慢。
我已经重写了它,以便只纠正不好的字符,也扩展到所有不可打印的字符和标准ASCII之外的字符。
Public Function Clean_NonPrintableCharacters(Str As String) As String
'Removes non-printable characters from a string
Dim cleanString As String
Dim i As Integer
cleanString = Str
For i = Len(cleanString) To 1 Step -1
'Debug.Print Asc(Mid(Str, i, 1))
Select Case Asc(Mid(Str, i, 1))
Case 1 To 31, Is >= 127
'Bad stuff
'https://www.ionos.com/digitalguide/server/know-how/ascii-codes-overview-of-all-characters-on-the-ascii-table/
cleanString = Left(cleanString, i - 1) & Mid(cleanString, i + 1)
Case Else
'Keep
End Select
Next i
Clean_NonPrintableCharacters = cleanString
End Function
这篇关于VBA:如何从数据中删除不可打印的字符的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文