时间戳

首页 > 解决方案 > 检测非标准符号

excel - 检测非标准符号

问题描述

我的 Excel 列表有问题。我有包含非标准符号的用户名。如何通过 VBA 检测或找到它们？任何人都可以为此提出解决方案吗？

其中一些在这里，但是有很多包含符号的用户名

等等

标签： excelvba

解决方案

Byte通过Array分析标准和非标准字符

s此示例使用分配给数组的变量的 OP 的第一行字符串“♫muz♫”，该Byte数组允许分析由每个字符两个字节表示的每个字符代码。这种 2 字节架构允许检测非标准字符，获取它们的十进制或十六进制值（~~> 参见ChrW和AscW函数）。此外，此示例将标准字符隔离在最终s变量中。随意在任何想要的方向修改代码:-)

暗示

可以在https://unicode-table.com/en/blocks/找到一个非常有用的通过直接输入搜索非标准字符的站点

Sub DedectNonStandardSymbols()
' Meth: analyze a Byte-array consisting of 2 bytes with a pair of character codes;
'       e.g. code sequence 96 followed by 0 represents the standard character "a"
' Note: VBA handles strings in Unicode encoded as UTF-16, so
'       each ‘character’ of the string corresponds at least to a 16 bit WORD (2 bytes) of memory.
' Hint: For details about Windows' Little Endian architecture
'       where the lowest significant byte appears first in memory, followed by the most significant byte at the next address.
'       see http://support.microsoft.com/kb/102025
  Dim by() As Byte, by2() As Byte, s As String
  Dim i&, ii&, dec&
' build example string (cf. 1st example string in OP)
  s = ChrW(&H266B) & "muz" & ChrW(&H266B)     ' beamed eighth notes surrounding the standard characters "muz"
' get byte sequences
  by = s: ReDim by2(0 To UBound(by))
' loop through array and detect non standard characters
  For i = LBound(by) To UBound(by) Step 2
      dec = by(i + 1) * 16 * 16 + by(i)       ' 2nd word (byte)
      Debug.Print i / 2 + 1, by(i) & ", " & by(i + 1), _
            "dec " & dec & "/hex &H" & WorksheetFunction.Dec2Hex(Format(dec, "0")), _
            "=> """ & ChrW(dec) & """" & IIf(by(i + 1) = 0, "", " (non-standard char)")
      If by(i + 1) = 0 Then
         by2(ii) = by(i): by2(ii + 1) = 0: ii = ii + 2
      End If
  Next i
  ReDim Preserve by2(0 To ii - 1)
  s = by2
  Debug.Print "String without non-standard chars: """ & s & """"
End Sub

VBE 的即时窗口中的示例输出

 1            107, 38       dec 9835/hex &H266B         => "?" (non-standard char)
 2            109, 0        dec 109/hex &H6D            => "m"
 3            117, 0        dec 117/hex &H75            => "u"
 4            122, 0        dec 122/hex &H7A            => "z"
 5            107, 38       dec 9835/hex &H266B         => "?" (non-standard char)
 String without non-standard chars: "muz"

推荐阅读