Results 1 to 3 of 3

Display control characters?

This is a discussion on Display control characters? within the Excel Questions forums, part of the Question Forums category; How do I get Excel to show characters such as tab characters, paragraph marks, Alt-Return. Something like MS Word does. ...

  1. #1
    Board Regular
    Join Date
    Oct 2002
    Posts
    657

    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.

    Thanks,
    Chas

  2. #2
    Board Regular
    Join Date
    Sep 2002
    Location
    Simsbury CT USA
    Posts
    1,542

    Default Re: Display control characters?

    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

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

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

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm
    (My other life: http://damonostrander.com )

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
  •  


DMCA.com