Display control characters?

[Chas17]

How do I get Excel to show characters such as tab characters, paragraph marks, Alt-Return. Something like MS Word does. Is this possible. I tried Tools-Options International Tab Show Control Characters. But this did nothing.

Thanks,
Chas

 

[plettieri]

Hi:

There is an addin on Pearson's web site....see Cell View...http://www.cpearson.com/excel/download.htm


This add-in allows you to see the characters and their codes in a cell. This makes it easy to find unprintable characters such as tabs that don't display but may affect formulas.

hope this helps
plettieri

 

[Damon Ostrander]

Hi Chas,

Here's a macro that will display the text in the currently active cell, displaying the control characters in brackets. It uses the codes shown in the Clist array below to identify the various control codes (e.g., LF = line feed).


Sub DisplayControlCharacters()
'Displays control characters in the currently selected
'cell in a message box wrapped in [] (e.g., [LF] is a
'linefeed character).

Dim Clist As Variant
Clist = Array("NUL", "SOH", "STX", "ETX", "EOT", "ENQ", "ACK", "BEL", _
"BS", "TAB", "LF", "VT", "FF", "CR", "SO", "SI", "DLE", _
"DC1", "DC2", "DC3", "DC4", "NAK", "SYN", "ETB", "CAN", _
"EM", "SUB", "ESC", "FS", "GS", "RS", "US")

If ActiveCell = WorksheetFunction.Clean(ActiveCell) Then
MsgBox "No special characters found", vbInformation, "Special Character Display"
Else
Dim OutStr As String
Dim Ch As String * 1
Dim i As Integer
With ActiveCell.Characters
For i = 1 To .Count
Ch = Mid(.Text, i, 1)
If Asc(Ch) < 32 Then
OutStr = OutStr & "[" & Clist(Asc(Ch)) & "]"
Else
OutStr = OutStr & Ch
End If
Next i
End With
MsgBox OutStr, vbInformation, "Special Characters Display"
End If

End Sub


To install this macro go to the VBE (keyboard Alt-TMV), then insert a new macro module (Alt-IM), paste the code into the Code pane. You can then run the macro using Alt-TMM. I recommend you assign a shortcut key using the Options button.