Removing of special charcters

Question

I have a VB code for excel here that removes the special characters. My question is why is some of the special characters are removed like for example some of the special characters in column c didn't change or got removed.

Code:

Sub Invalid()



    Dim e

    For Each e In Array("â€“", "€", "â", "¦", "Â", "®", "&reg;", "&#8212;", "Ã", "Ã±a", "±a", "¡c", "±", "&#39;", "&#x96;", "Ã³", "â€‹", "â€¦", "Â®", "‹")

        If e Like "[â€“]" Then
            Columns("a").Replace "â€“", ""
            Columns("b").Replace "â€“", ""
            Columns("c").Replace "â€“", ":"
            Columns("d").Replace "â€“", ""
            Columns("e").Replace "â€“", ":"
       Else
       
        If e Like "[Ã³]" Then
            Columns("a").Replace "Ã³", ""
            Columns("b").Replace "Ã³", ""
            Columns("c").Replace "Ã³", "o"
            Columns("d").Replace "Ã³", ""
            Columns("e").Replace "Ã³", ""
       Else
       
         If e Like "[Ã±a]" Then
            Columns("a").Replace "Ã±a", ""
            Columns("b").Replace "Ã±a", ""
            Columns("c").Replace "Ã±a", ""
            Columns("d").Replace "Ã±a", ""
            Columns("e").Replace "Ã±a", ""
       Else
       
         If e Like "[â€‹]" Then
            Columns("a").Replace "â€‹", ""
            Columns("b").Replace "â€‹", ""
            Columns("c").Replace "â€‹", ""
            Columns("d").Replace "â€‹", ""
            Columns("e").Replace "â€‹", ""
       Else
       
         If e Like "[â€¦]" Then
            Columns("a").Replace "â€¦", ""
            Columns("b").Replace "â€¦", ""
            Columns("c").Replace "â€¦", ""
            Columns("d").Replace "â€¦", ""
            Columns("e").Replace "â€¦", ""
       Else
         If e Like "[Â®]" Then
            Columns("a").Replace "Â®", ""
            Columns("b").Replace "Â®", ""
            Columns("c").Replace "Â®", ""
            Columns("d").Replace "Â®", ""
            Columns("e").Replace "Â®", ""
      Else
      
         If e Like "[€]" Then
            Columns("a").Replace "€", ""
            Columns("b").Replace "€", ""
            Columns("c").Replace "€", ""
            Columns("d").Replace "€", ""
            Columns("e").Replace "€", ""
      Else
      
         If e Like "[â]" Then
            Columns("a").Replace "â", ""
            Columns("b").Replace "â", ""
            Columns("c").Replace "â", ""
            Columns("d").Replace "â", ""
            Columns("e").Replace "â", ""
      Else
      
         If e Like "[¦]" Then
            Columns("a").Replace "¦", ""
            Columns("b").Replace "¦", ""
            Columns("c").Replace "¦", ""
            Columns("d").Replace "¦", ""
            Columns("e").Replace "¦", ""
      Else
      
         If e Like "[Â]" Then
            Columns("a").Replace "Â", ""
            Columns("b").Replace "Â", ""
            Columns("c").Replace "Â", ""
            Columns("d").Replace "Â", ""
            Columns("e").Replace "Â", ""
      Else
      
         If e Like "[®]" Then
            Columns("a").Replace "®", ""
            Columns("b").Replace "®", ""
            Columns("c").Replace "®", ""
            Columns("d").Replace "®", ""
            Columns("e").Replace "®", ""
      Else
      
         If e Like "[&reg;]" Then
            Columns("a").Replace "&reg;", ""
            Columns("b").Replace "&reg;", ""
            Columns("c").Replace "&reg;", ""
            Columns("d").Replace "&reg;", ""
            Columns("e").Replace "&reg;", ""
      Else
      
         If e Like "[&#8212;]" Then
            Columns("a").Replace "&#8212;", ""
            Columns("b").Replace "&#8212;", ""
            Columns("c").Replace "&#8212;", ""
            Columns("d").Replace "&#8212;", ""
            Columns("e").Replace "&#8212;", ""
      Else
      
         If e Like "[Ã]" Then
            Columns("a").Replace "Ã", ""
            Columns("b").Replace "Ã", ""
            Columns("c").Replace "Ã", ""
            Columns("d").Replace "Ã", ""
            Columns("e").Replace "Ã", ""
      Else
      
         If e Like "[±a]" Then
            Columns("a").Replace "±a", ""
            Columns("b").Replace "±a", ""
            Columns("c").Replace "±a", ""
            Columns("d").Replace "±a", ""
            Columns("e").Replace "±a", ""
      Else
      
         If e Like "[¡c]" Then
            Columns("a").Replace "¡c", ""
            Columns("b").Replace "¡c", ""
            Columns("c").Replace "¡c", ""
            Columns("d").Replace "¡c", ""
            Columns("e").Replace "¡c", ""
      Else
      
         If e Like "[±]" Then
            Columns("a").Replace "±", ""
            Columns("b").Replace "±", ""
            Columns("c").Replace "±", ""
            Columns("d").Replace "±", ""
            Columns("e").Replace "±", ""
      Else
      
         If e Like "[&#39;]" Then
            Columns("a").Replace "&#39;", ""
            Columns("b").Replace "&#39;", ""
            Columns("c").Replace "&#39;", ""
            Columns("d").Replace "&#39;", ""
            Columns("e").Replace "&#39;", ""
        Else
        
        If e Like "[&#x96;]" Then
            Columns("a").Replace "&#x96;", ""
            Columns("b").Replace "&#x96;", ""
            Columns("c").Replace "&#x96;", ""
            Columns("d").Replace "&#x96;", ""
            Columns("e").Replace "&#x96;", ""
        Else
        
        If e Like "[‹]" Then
            Columns("a").Replace "‹", ""
            Columns("b").Replace "‹", ""
            Columns("c").Replace "‹", ""
            Columns("d").Replace "‹", ""
            Columns("e").Replace "‹", ""

    Else
        
        If e Like "[&#x96;]" Then
            Columns("a").Replace "&#x96;", ""
            Columns("b").Replace "&#x96;", ""
            Columns("c").Replace "&#x96;", ":"
            Columns("d").Replace "&#x96;", ""
            Columns("e").Replace "&#x96;", ""
        


End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
Next


End Sub

That is a fine example of the [for-if antipattern](https://devblogs.microsoft.com/oldnewthing/20111227-00/?p=8793). — GSerg, Aug 10 '20 at 18:42
Apart from the above: VBA puts no special meaning in HTML-specific sequences such as `®` or `—`, so those are taken literally. Then you place these literal strings inside the `[]` brackets of the `Like` pattern, thus requesting an OR-match on any of the individual letters (which therefore will only match when `e` is one literal character to begin with). Finally, you [cannot have](https://stackoverflow.com/a/25260658/11683) literal Unicode characters in the VBA source code. — GSerg, Aug 10 '20 at 18:47

Tim Williams · Answer 1 · 2020-08-10T19:14:17.340

Shorter:

Sub Invalid()

    Dim e

    For Each e In Array("â€“", "€", "â", "¦", "Â", "®", "&reg;", "&#8212;", _
                        "Ã", "Ã±a", "±a", "¡c", "±", "&#39;", "&#x96;", "Ã³", _
                        "â€‹", "â€¦", "Â®", "‹")

        Select Case e
            Case "â€“"
                Range("A1:B1,D1").EntireColumn.Replace e, ""
                Range("C1,E1").EntireColumn.Replace e, ":"
            Case "Ã³"
                Range("A1:B1,D1:E1").EntireColumn.Replace e, ""
                Range("C1").EntireColumn.Replace e, "o"
            Case "&#x96;"
                Range("A1:B1,D1:E1").EntireColumn.Replace e, ""
                Range("C1").EntireColumn.Replace e, ":"
            Case Else
                Range("A:E").Replace e, ""
        End Select
    Next e

End Sub

For the range that is A1, B1, and D1, does it only check the first cell? — user14082640, Aug 10 '20 at 19:28

score -3 · Answer 2 · answered Aug 10 '20 at 18:49

Long time ago I found a function that (I think) can do what you want, in a better way:

Public Function RepLetters(txt As String) As String
 
Dim SAcentos As String
Dim SSemAcentos As String
Dim STemp As String
Dim i As Long

'All letters to be replaced
SAcentos = "àáâãäèéêëìíîïòóôõöùúûüÀÁÂÃÄÈÉÊËÌÍÎÒÓÔÕÖÙÚÛÜçÇñÑ"
  
'Letters to replace on
SSemAcentos = "aaaaaeeeeiiiiooooouuuuAAAAAEEEEIIIOOOOOUUUUcCnN"
  
'sTemp is the received string
STemp = text
  
' Loop through every letter of 'sAcentos' and replace by
' corresponding letter in 'sSemAcentos'
For i = 1 To Len(SAcentos)
    STemp = Replace(STemp, Mid$(SAcentos, i, 1), Mid$(SSemAcentos, i, 1))
Next i
  
'return new string
RepLetter = STemp
  
End Function

You will need to adapt sAcentos and sSemAcentos for your needs. This function can be called directly on spreadsheet too.

OP is not attempting a simple 1 for 1 character replacement for which this code is ostensibly written. — Michael Murphy, Aug 10 '20 at 20:29

Removing of special charcters

2 Answers2