Hide non-printing characters.

C

Chris Watts

I have imported some data into Excel as a CSV file. Some of the cells
contain formatting character such as CR, LF, HT.
One one machine, that uses Excel 2000, these characters are displayed as
squares. On another macine, that uses Excel 2007, the characters are still
present but do not display.
How can I hide (not delete) those characters on the Excel 2000 machine?

TIA
Chris
 
D

Dave Peterson

Linefeeds will appear as little squares if the cell's format is not set to wrap
text.

If you change the format to wrap text, then the linefeeds will force new lines
within the cell.

Saved from a previous post.

Chip Pearson has a very nice addin that will help determine what that
character(s) is:
http://www.cpearson.com/excel/CellView.aspx

Depending on what that character is, you may be able to use alt-#### (from the
number keypad) to enter the character into the Other box in the text to columns
wizard dialog.

In fact, you may be able to select the character (in the formula bar), and copy
it. Then use ctrl-v to paste into that text to columns Other box.

You may be able to use Edit|Replace to change the character--Some characters can
be entered by holding the alt-key and typing the hex number on the numeric
keypad. For example, alt-0010 (or ctrl-j) can be used for linefeeds. But I've
never been able to get alt-0013 to work for carriage returns.

Another alternative is to fix it via a formula:

=substitute(a1,char(##),"")

Replace ## with the ASCII value you see in Chip's addin.

Or you could use a macro (after using Chip's CellView addin):

Option Explicit
Sub cleanEmUp()

Dim myBadChars As Variant
Dim myGoodChars As Variant
Dim iCtr As Long

myBadChars = Array(Chr(##)) '<--What showed up in CellView?

myGoodChars = Array("")

If UBound(myGoodChars) <> UBound(myBadChars) Then
MsgBox "Design error!"
Exit Sub
End If

For iCtr = LBound(myBadChars) To UBound(myBadChars)
ActiveSheet.Cells.Replace What:=myBadChars(iCtr), _
Replacement:=myGoodChars(iCtr), _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False
Next iCtr

End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)
 
C

Chris Watts

Thanks Dave.
As you say wrap text removes the LF square - but not the others in 2000.
cheers
Chris
 
J

jw hovenga

Can anyone answer the main question: how to show or hide these squares in Excel?
Why does one machine show the squares and a second one doesn't? What is the option in Excel for show/hide the squares?
Thanks
 
D

Dave Peterson

I wouldn't call the CR and LF characters formatting characters in excel. I
don't know what the HT character means.

And I'm surprised that excel imported that data correctly from your CSV file.

But if the cell is formatted to wraptext, then the linefeeds will force newlines
within the cell. The CR will still see the box symbol in the cell.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top