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

Answer 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:

The formula in B2 is:

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