Checking for special characters in excel using vba

Question

. I have an excel spreadsheet which contains some strings with unicode control characters that aren't visible in Windows 7. Therefore, I would like to write a macro to iterate through each cell in a column, checking if a control character is present. If a control character is found, I would like to populate the adjacent cell in the next column with the character name and the index it can be found within the string.

This is what I have so far:

Sub control_chr()
'
' control_chr Macro
'

'
    Dim control_characters(Chr(28), Chr(29), Chr(30), Chr(31), Chr(32))
    Dim r As Range, cell As Range

    Set r = Range("F4:F1029")

    For Each cell In r
        For Each Character In control_characters

the next step would be to search the cell for each character and populate adjacent cells with the results. My first thought was to use the SEARCH() function since it returns the index of where the character is found. This is my first time using visual basic and I'm not sure how to proceed

Are you interested in a non-VBA solution?

Doug Glancy
– Doug Glancy

2013-08-15 14:45:10 +00:00
Commented Aug 15, 2013 at 14:45 — Doug Glancy
– Doug Glancy, Commented Aug 15, 2013 at 14:45

Doug Glancy · Accepted Answer · 2013-08-15 15:03:00Z

1

Here's some code that does what you asked:

Sub ListControlChars()
Dim control_characters As Variant
Dim r As Range, cell As Range, ResultCell As Range
Dim CharPosition As Long
Dim i As Long

control_characters = Array(28, 29, 30, 31, 32)
Set r = ActiveSheet.Range("F4:F1029")

For Each cell In r
    Set ResultCell = cell.Offset(0, 1)
    ResultCell.ClearContents
    CharPosition = 0
    For i = LBound(control_characters) To UBound(control_characters)
    CharPosition = InStr(cell, Chr(control_characters(i)))
        If CharPosition > 0 Then
        ResultCell = ResultCell.Value & "Char " & control_characters(i) & ": Position " & CharPosition & " - "
        End If
    Next i
Next cell
End Sub

If you want to do it in Excel you could set it up like this:

enter image description here

The formula in B2 is:

=IFERROR(SEARCH(CHAR(B$1),$A2),"")

answered Aug 15, 2013 at 15:03

Doug Glancy

27.5k6 gold badges72 silver badges118 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

compscimaster Over a year ago

This seems to do exactly what I'm looking for, but I cant get it to run. I keep getting a type mismatch error on the following line: CharPosition = Instr(cell, Chr(control_characters(i))) This is my first time working with vba, not sure what could be causing this

Doug Glancy Over a year ago

Also, is it the first cell in the range, i.e., F4?

compscimaster Over a year ago

Yes, first cell is in the range. Cell isn't getting assigned a proper value, getting "Error 2015"

Doug Glancy Over a year ago

Let me ask another way. What's the address of cell when you get the error? And what is the actual value in that cell when you get the error?

compscimaster Over a year ago

nevermind, I got it. One of the cells had a null value. Thank you for your help

JonoMac · Accepted Answer · 2022-01-14 22:44:59Z

I can't recall if I coded this up or found it somewhere else, but this works for me when encountering issues with loading data into certain databases which fail on unrecognised special characters

Function IterateThruCells()    
Dim cell As Range    
   For Each cell In ActiveSheet.UsedRange.Cells
       If cell.Value <> "" Then
          If ContainsSpecialCharacters(cell.Value) = True Then
              Debug.Print cell.Address & ": " & cell.Value
          End If
       End If
   Next
End Function


               
Function ContainsSpecialCharacters(str As String) As Boolean
Dim I
For I = 1 To Len(str)
    ch = Mid(str, I, 1)
    Select Case ch
         Case "0" To "9", "A" To "Z", "a" To "z", " ", "(", ")", "/", "-", ".", ",", "_", "&", "'", "$", ">", "<", "–"
         ContainsSpecialCharacters = False
            
    Case Else
         ContainsSpecialCharacters = True
         Exit For    
    End Select
           
Next
End Function

Collectives™ on Stack Overflow

Checking for special characters in excel using vba

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related