好吧,我已经创建了(在 StackOverflow 的最佳帮助下)一个用户定义函数,它标识给定单元格内特定的(不可见的)、不可打印的控制字符。然后,UDF 创建一个消息框,告诉用户已找到并删除了哪些字符。
这些函数不会费心去定位 32 个 ASCII 控制字符中的每一个,它只会查找在 Excel 中没有图形表示的字符。
这是当前状态的函数:
Function findInvisChar(sInput As String) As String
Dim sSpecialChars As String
Dim i As Long
Dim sReplaced As String
Dim ln As Integer
sSpecialChars = "" & Chr(1) & Chr(2) & Chr(3) & Chr(4) & Chr(5) & Chr(6) & Chr(7) & Chr(8) & Chr(9) & Chr(10) & Chr(11) & Chr(12) & Chr(13) & Chr(14) & Chr(15) & Chr(16) & Chr(17) & Chr(18) & Chr(19) & Chr(20) & Chr(21) & Chr(22) & Chr(23) & Chr(24) & Chr(25) & Chr(26) & Chr(27) & Chr(28) & Chr(29) & Chr(30) & Chr(31) & Chr(32) 'This is your list of characters to be removed
'For loop will repeat equal to the length of the sSpecialChars string
'loop will check each character within sInput to see if it matches any character within the sSpecialChars string
For i = 1 To Len(sSpecialChars)
ln = Len(sInput) 'sets the integer variable 'ln' equal to the total length of the input for every iteration of the loop
sInput = Replace$(sInput, Mid$(sSpecialChars, i, 1), "")
'If ln <> Len(sInput) Then sReplaced = sReplaced & Mid$(sSpecialChars, i, 1)
If ln <> Len(sInput) Then sReplaced = sReplaced & IIf(Mid$(sSpecialChars, i, 1) = Chr(10), "<Line Feed>", Mid$(sSpecialChars, i, 1)) & IIf(Mid$(sSpecialChars, i, 1) = Chr(1), "<Start of Heading>", Mid$(sSpecialChars, i, 1)) & IIf(Mid$(sSpecialChars, i, 1) = Chr(9), "<Character Tabulation, Horizontal Tabulation>", Mid$(sSpecialChars, i, 1)) & IIf(Mid$(sSpecialChars, i, 1) = Chr(13), "<Carriage Return>", Mid$(sSpecialChars, i, 1)) & IIf(Mid$(sSpecialChars, i, 1) = Chr(28), "<File Separator>", Mid$(sSpecialChars, i, 1)) & IIf(Mid$(sSpecialChars, i, 1) = Chr(29), "<Group separator>", Mid$(sSpecialChars, i, 1)) & IIf(Mid$(sSpecialChars, i, 1) = Chr(30), "<Record Separator>", Mid$(sSpecialChars, i, 1)) & IIf(Mid$(sSpecialChars, i, 1) = Chr(31), "<Unit Separator>", Mid$(sSpecialChars, i, 1)) & IIf(Mid$(sSpecialChars, i, 1) = ChrW(A0), "<Non-Breaking Space>", Mid$(sSpecialChars, i, 1)) 'Currently will remove all control character but only tell the user about invisible characters which were removed
Next
MsgBox sReplaced & "These were removed"
sInput = UCase(sInput)
findSpecial = sInput
End Function 'end of function
我想做的就是让这个函数识别不间断的空格字符。它们的 unicode 值为 U+00A0。这是我创建的代码部分,用于识别不间断空格(如果它确实出现在目标单元格中):
IIf(Mid$(sSpecialChars, i, 1) = ChrW(A0), "<Non-Breaking Space>", Mid$(sSpecialChars, i, 1))
我在使用 ChrW() 函数时遇到问题。据我了解,它接受 unicode 字符的十六进制值,在这种情况下,该值将是A0
, 如果我没有记错的话。
我在Excel文档中放置了一个不间断的空格来测试该功能,但是当我这样做时,该功能不起作用。消息框只是显示为空白,而不是告诉用户“...这些字符已被删除。
我是否错误地使用了 ChrW() 函数?或者我的代码或测试方法是否存在我可能遗漏的其他问题?