Re: Hide non-printing characters.

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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

"Dave Peterson" <petersod@xxxxxxxxxxxxxxxx> wrote in message
news:4B263E7C.EDC67309@xxxxxxxxxxxxxxxxxxx
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.)

Chris Watts wrote:

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

--

Dave Peterson


.

Relevant Pages

  • Re: Carriage return displays as a small box
    ... My question was regarding the excel settings. ... Carriage returns or vbcr) will show up as little squares. ... number keypad) to enter the character into the Other box in the text to columns ... Debra Dalgleish has some notes how to implement macros here: ...
    (microsoft.public.excel.misc)
  • Re: how to replace an unrecognised character
    ... number keypad) to enter the character into the Other box in the text to columns ... Replace ## with the ASCII value you see in Chip's addin. ... Debra Dalgleish has some notes how to implement macros here: ... I am importing data to Excel from a flat file - and the rows being imported ...
    (microsoft.public.excel.misc)
  • Export to xls wrap problem
    ... I have created a basic database, including macros etc (I'm only a beginner, ... Excel format. ... When this file is opened in Excel, the cells are ... the cells not to wrap, ...
    (microsoft.public.access.gettingstarted)
  • Re: Programming Line Breaks into Cells
    ... Did you turn on Cell wrap Format, cells, alignment, wrap ... Perhaps you have a different character ... Microsoft MVP - Excel ...
    (microsoft.public.excel.programming)
  • Re: Ken Snell (MVP)
    ... should be no need to put a ' character in front of the field in the query. ... My testing shows that a text field is exported to EXCEL as a text field, ... Export to EXCEL option from the report, ...
    (microsoft.public.access.reports)