使用VBA识别并删除电子表格中的不间断空格字符 [英] use VBA to identify and remove a non-breaking space character in a spreadsheet

问题描述

好的，我已经创建了一个用户定义函数(在StackOverflow的最好帮助下)，该函数定义了给定单元格中特定的(不可见的)不可打印的控制字符.然后，UDF创建一个消息框，告知用户已找到并删除了哪个字符.

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.

该函数不会费心查找32个ASCII控制字符中的每一个，它只会对在Excel中没有图形表示的一个字符进行罚款.

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.

以下是当前状态下的函数:

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

我正在尝试做的是还要使此功能识别不间断的空格字符.它们具有U + 00A0的Unicode值.如果实际上它确实出现在目标单元格中​​，这就是我创建的用于标识不间断空格的代码部分:

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:

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

我在使用ChrW()函数时遇到麻烦.据我了解，它接受Unicode字符的十六进制值，在这种情况下，如果我没有记错的话，该值将为A0.

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.

我已经在Excel文档中放置了一个不间断的空格来测试该功能，但是当我这样做时，该功能将无法正常工作.消息框仅显示为空白，而不是告诉用户"...这些字符已删除.

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.

我不正确地使用ChrW()函数吗?还是我的代码或测试方法可能会遗漏另一个问题?

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

推荐答案

我将回答我自己的问题，这样就不会在没有答案的情况下徘徊.

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

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

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.

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

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

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

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

这篇关于使用VBA识别并删除电子表格中的不间断空格字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！