Detecting non-standard symbols

Question

I have a problem with my Excel list. I have names of users which include non-standard symbols. how can I detect or find them by VBA? can anyone suggest solution for this?

Some of them here, but there are a lot of user names that contain symbols

♫muz♫
BOSS
h❤️
name☝

and so on

Start here:[Removing special characters VBA Excel](https://stackoverflow.com/a/43166192/9912714) — TinMan, Dec 26 '18 at 14:10
Where do these symbols come from? What's the origin of the data in the list? Are these non-Latin characters? Then it may be a question of the *font* with which the cells have been formatted. Try applying a Unicode font to them. — Cindy Meister, Dec 26 '18 at 14:10
Added a tricky solution permitting to analyze your string inputs via so called `Byte` arrays. - Further hint: It's good use and also helpful for other readers to mark one of the received answers as accepted if you found it helpful (acceptance is indicated by a colored checkmark next to the answer). C.f. ["Someone answers"](https://stackoverflow.com/help/someone-answers) — T.M., Dec 28 '18 at 17:45

score 0 · Answer 1 · answered Dec 26 '18 at 18:58

The best method is to validate each character in the string so that you can eliminate any symbols or characters that are not expected. Here's the code for a simple function that checks if a string has ONLY lowercase or uppercase letters (no spaces or special characters/symbols).

Public Function IsValidName(Name As String) As Boolean

    Dim ValidCharacters As String, Position As Integer, Character As String

    'STEP 1: set up valid characters for string
    ValidCharacters = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"

    'STEP 2: start position at first character
    Position = 1

    While Position <= Len(Name)
        'Get single character from current position in Name
        Character = Mid(Name, Position, 1)

        'Locate the position of the character in the ValidCharacters string
        '   If InStr() returns 0, then the character was not found in the string
        If InStr(ValidCharacters, Character) = 0 Then
            IsValidName = False
            Exit Function
        End If

        'Increment position
        Position = Position + 1
    Wend

    'STEP 3: all characters were found in ValidCharacters string - return TRUE
    IsValidName = True
End Function

You can also modify the function to parse out any symbols in the name and return only the valid characters. See the function below:

Public Function ParseName(Name As String) As String

    Dim ValidCharacters As String, Position As Integer, Character As String, ValidName As String

    'STEP 1: set up valid characters for string
    ValidCharacters = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"

    'STEP 2: start position at first character
    Position = 1

    While Position <= Len(Name)
        'Get single character from current position in Name
        Character = Mid(Name, Position, 1)

        'Locate the position of the character in the ValidCharacters string
        '   If InStr() is NOT 0, then the character was found and can be added to the ValidName
        If InStr(ValidCharacters, Character) <> 0 Then
            ValidName = ValidName & Character
        End If

        'Increment position
        Position = Position + 1
    Wend

    'STEP 3: all characters were found in ValidCharacters string - return TRUE
    ParseName = ValidName
End Function

score 0 · Answer 2 · answered Dec 28 '18 at 16:54

Analyze standard and non-standard characters via Byte Array

This example uses OP's first line string "♫muz♫" of variable s assigned to a Byte array which allows to analyze each character code represented by two bytes per character. This 2-byte architecture allows to detect non-standard characters, get their decimal or hexadecimal values (~~> cf. ChrW and AscW function). Furthermore this example isolates the standard characters in a final s variable. Feel free to modify code in any wanted direction :-)

Hint

A very helpful site to search non-standard characters by direct Input can be found at 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

Example output in VBE's immediate window

 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"

Detecting non-standard symbols

2 Answers2