Results 1 to 3 of 3

Thread: Display control characters?

  1. #1
    Board Regular
    Join Date
    Oct 2002

    Default Display control characters?

    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.


  2. #2
    Board Regular
    Join Date
    Sep 2002
    Simsbury CT USA

    Default Re: Display control characters?


    There is an addin on Pearson's web site....see Cell View...

    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

  3. #3
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Denver, Colorado USA

    Default Re: Display control characters?

    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"
    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)) & "]"
    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.
    Keep Excelling.


    VBAexpert Excel Consulting
    LinkedIn Profile
    AllExperts Profile

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts