VBA：如何从数据中删除不可打印的字符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41882525/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA: How to remove non-printable characters from data
提问by erasmo carlos
I need to remove programmatically non printable characters such as:
我需要以编程方式删除不可打印的字符，例如：
tabs - char(9) line breaks - char(10) carriage return - char(13) data link escape - char(16)
制表符 - char(9) 换行符 - char(10) 回车 - char(13) 数据链接转义 - char(16)
I started a generic function that will be called from the lost_focus event of the ms access form field.
我启动了一个通用函数，该函数将从 ms 访问表单字段的 lost_focus 事件中调用。
I have not figured out how to identify when the string contains the unwanted characters.
我还没有弄清楚如何识别字符串何时包含不需要的字符。
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
These are 2 strings I have been using whilst testing:
这些是我在测试时一直使用的 2 个字符串：
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
回答by Freddie
A = Chr(09) & "Cat" & Chr(10) & vbcrlf
A = Replace(A, Chr(10))
A = Replace(A, Chr(13))
A = Replace(A, Chr(09))
Msgbox A
This is how one normally does it.
这是人们通常的做法。
Your code is creating a lot of implicit variables.
您的代码正在创建许多隐式变量。
回答by Dave Scott
This is the top google result when I search for a quick function to use, I've had a good old google but nothing that solves my issue fully has really come up.
当我搜索要使用的快速功能时，这是最重要的谷歌结果，我有一个很好的旧谷歌，但没有真正解决我的问题。
The main issue is that all of these functions touch the original string even if there's no issue. Which slows stuff down.
主要问题是所有这些函数都会触及原始字符串，即使没有问题。这会减慢速度。
I've rewritten it so that only amends if bad character, also expanded to all non-printable characters and characters beyond standard ascii.
我已经重写了它，以便仅在错误字符时进行修改，还扩展到所有不可打印的字符和超出标准 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
回答by RyanL
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, 10, 13, 16
cleanString = cleanString & " "
Case Else
cleanString = cleanString & Mid(dirtyString, iPosition, 1)
End Select
Next
RemoveNonPrintableCharacters = cleanString
End Function
回答by arif
'first you need to find a character
'首先你需要找到一个角色
YourStr = "Bla bla bla..."
YourStr = "Bla bla bla..."
if instr(YourStr, chr(10)) > 0 then
如果 instr(YourStr, chr(10)) > 0 那么
NewStr = Replace(YourStr, Chr(10),"")
end if
万一
回答by RyanL
I'm replacing non-printable characters with a space character chr(32) but you can alter this to suit your needs.
我正在用空格字符 chr(32) 替换不可打印的字符，但您可以更改它以满足您的需要。
Function RemoveNonPrintableCharacters(ByVal TextData) As String
Dim sClean$
sClean = Replace(TextData, Chr(9), Chr(32))
sClean = Replace(sClean, Chr(10), Chr(32))
sClean = Replace(sClean, Chr(13), Chr(32))
sClean = Replace(sClean, Chr(16), Chr(32))
RemoveNonPrintableCharacters = sClean
End Function
回答by Bill
This works to remove Non-printing Characters from the right side of the string only and do not replace the characters with spaces.
这仅用于从字符串的右侧删除非打印字符，而不用空格替换字符。
Function fRemoveNonPrintableCharacters(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 = Len(dirtyString) To 1 Step -1
Select Case Asc(Mid(dirtyString, iPosition, 1))
Case 9, 10, 13, 16, 32, 160
cleanString = cleanString
Case Else
cleanString = Left(dirtyString, iPosition)
Exit For
End Select
Next
fRemoveNonPrintableCharacters = cleanString
End Function
回答by Lyspon
It seems as if this should be much simpler, using the Excel Clean function. The following also works:
使用 Excel Clean 函数，这似乎应该简单得多。以下也有效：
myString = Worksheets("Sheet1").Range("A" & tRow).Value
myString = Application.WorksheetFunction.Clean(myString)
You can also use other normal and home-grown Excel functions:
您还可以使用其他普通的和自产的 Excel 函数：
myString = Application.WorksheetFunction.Trim(myString)
Still haven't gotten the Substitute function to work in this way, but I'm working on it.
仍然没有让 Substitute 功能以这种方式工作，但我正在努力。
