使用 VBA 识别和删除电子表格中的不间断空格字符

use VBA to identify and remove a non-breaking space character in a spreadsheet

提问by

Alright, I have created (with the best help from StackOverflow) a User Defined Function, which Identifies specific (invisible), non-printable control characters within a given cell. The UDF then creates a message box telling the user what character has been found and removed.

好的，我已经创建了（在 StackOverflow 的最佳帮助下）一个用户定义的函数，它可以识别给定单元格内的特定（不可见）、不可打印的控制字符。然后，UDF 创建一个消息框，告诉用户已找到并删除了哪些字符。

The functions does not bother to locate every one of the 32 ASCII control characters, it only fines the one which don't have a graphical representation in Excel.

这些函数不会费心定位 32 个 ASCII 控制字符中的每一个，它只会对在 Excel 中没有图形表示的那个字符进行罚款。

Here is the function in it's current state:

这是当前状态下的函数：

     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

What I am trying to do, is to also make this function identify non-breaking space characters. These have a unicode value of U+00A0. This is the portion of code which I have created to identify a non-breaking space, if it in fact does appear in a target cell:

我想做的是让这个函数识别不间断的空格字符。它们的 unicode 值为 U+00A0。这是我创建的代码部分，用于识别不间断空格，如果它确实出现在目标单元格中​​：

IIf(Mid$(sSpecialChars, i, 1) = ChrW(A0), "<Non-Breaking Space>", Mid$(sSpecialChars, i, 1))

I am having trouble with the ChrW() function. To my understanding it accepts the hexadecimal value of a unicode character, and in this case that value would be A0, if I am not mistaken.

我在使用 ChrW() 函数时遇到问题。据我了解，它接受 unicode 字符的十六进制值A0，如果我没记错的话，在这种情况下，该值将是。

I have placed a non-breaking space into an excel document to test the function, but when I do, the function does not work. The messaage box simply shows up blank, instead of telling the user that "...These characters were removed.

我已经在一个 excel 文档中放置了一个不间断空格来测试该功能，但是当我这样做时，该功能不起作用。消息框只是显示为空白，而不是告诉用户“...这些字符已被删除。

Am I using the ChrW() function incorrectly? Or is there another issue with my code or testing method that I may be missing?

我是否错误地使用了 ChrW() 函数？或者我的代码或测试方法是否存在另一个我可能遗漏的问题？

采纳答案by

I will answer my own question so that it does not linger without an answer.

我将回答我自己的问题，以便它不会在没有答案的情况下徘徊。

The issue was that I had not previously told the function to find and remove the non-breaking space, in my string variable list of characters to be removed, sSpecialChars. I added ChrW(&HA0)to that string and now everything works swimmingly.

问题是我之前没有告诉函数在要删除的字符串变量列表 sSpecialChars 中查找和删除不间断空格。我添加ChrW(&HA0)到那个字符串，现在一切正常。

Thank you Portland Runner for showing me the proper way of using the ChrW() function.

感谢 Portland Runner 向我展示了使用 ChrW() 函数的正确方法。

For reference, my final code is as follows:

作为参考，我的最终代码如下：

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) & ChrW(&HA0) '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(&HA0), "<Non-Breaking Space>", Mid$(sSpecialChars, i, 1)) 'Currently will remove all control character but only tell the user about Bell and Line Feed
Next

MsgBox sReplaced & " These were identified and removed"
findInvisChar = sInput


End Function 'end of function