Excel不可见问号 [英] Excel invisible question mark

问题描述

我将系统中的信息提取到Excel文件中. 名称"Leone"似乎相同，但是Excel识别的方式不同.

I have an extracted information from a system into an Excel file. The names "Leone" seem the same but Excel recognize it differently.

利昂

利昂

字符串的长度不一样，如果我用VBA检查值，则看不见?是第一个字符.

The length of the string is not the same, and if I check the value with VBA an invisible ? is the first character.

您能帮我摆脱那些看不见的字符吗?

Could you help me how to get rid of the invisible characters?

推荐答案

要摆脱所有不可见的?，您可以尝试这样做.

To get rid of all invisible ? you may try this.

Sub CleanUnicode()
    Dim n As Long, strClean As String, strChr As String
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Sheets("Sheet3")  'change Sheet3 to data sheet
    For Each cel In ws.Range("A1:A10")      'change A1:A10 to working range
        strClean = cel.Value
        For n = Len(strClean) To 1 Step -1
            strChr = Mid(strClean, n, 1)
            If AscW(strChr) = 8203 Then     '? is unicode character 8203
                strClean = Replace(strClean, strChr, "")
            End If
        Next
        cel.Value = WorksheetFunction.Trim(strClean)
    Next cel
End Sub

您也可以使用If AscW(strChr) > 255 Then代替If AscW(strChr) = 8203 Then.

根据@ YowE3K的建议.假设您要替换的单元格中只有Unicode 8203.

EDIT 1 : As per the suggestion of @YowE3K. Assuming you only have Unicode 8203 in cells to be replaced.

Sub CleanUnicode()
    Dim n As Long, strClean As String, strChr As String
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Sheets("Sheet3")  'change Sheet3 to data sheet
    For Each cel In ws.Range("A1:A10")      'change A1:A10 to working range
        cel.Value = Replace(cel.Value, ChrW(8203), "")
    Next cel
End Sub

从这里.

这篇关于Excel不可见问号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！