Carriage return displays as a small box

[Merc]

I have an excel file with cells having data copied and pasted form word
document.
When I open this excel file, i nsome systems I see a small box for al lthe
carriage returns.
In some other sytems the carriage returns are displayed as simple blank
spaces.
What is the setting that can avoid dispaly of carriage returns as small boxes?

Thanks
Mercy

 

[Joel]

There arre two things you can do. Not sure you will be happy with either
results because I don't know if you want the the text to appearr on a
seperate line or be continuous.

1) Go to menu : Format - Cells - Allignment
And change the setting on Wrap Text.

2) Use the worksheet Replace option. Highlight one of the square boxes with
mouse and Copy (Cntl-C). Then select entire worksheet (Cntl-A). go to menu

Edit - Replace

Click on From box and paste the square box using Cntl-V. It won't look like
the square box. Then press Replace ALL button.

 

[Ms-Exl-Learner]

Try this.

=SUBSTITUTE(A1,CHAR(10),CHAR(32))

change the cell reference A1 to your cell and drag it upto the range you
want. Then copy and past it as values.

If this post helps, click Yes!

 

[Dave Peterson]

Those little boxes could be different characters.

Line feeds (=char(10) as a worksheet formula or vblf in code) will show up as
little squares if you haven't turned on wrap text. After you toggle the wrap
text setting, then you'll see multiple lines within the cell.

Carriage returns (=char(13) or vbcr) will show up as little squares. The wrap
text setting won't change them.

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.)

 

[Merc]

This is an excel file generated , so the formatting is already set. The file
has merge cells and word wrap set. The file generated fro ma .NET application
displays carriage return as small boxes in some systems and does not show the
small boxes in some systems.

 

[Merc]

Thanks for your answer. My question was regarding the excel settings. I have
been able to remove the charcaters using .NET code.

The question is related to different display in different systems.

 

[Dave Peterson]

There is no setting to hide/display carriage returns. There is a setting to
hide/show linefeeds (using wraptext).

 

[Mitch]

I am having the same problem on one PC I can see the boxes on another I can
not see them. I am wondering if there is a setting on one PC that is
different which is allowing me to see them. Or possibly its a bug and one of
my PC's has an older version of Excel. I am not sure. Did you find the answer

 

[Dave Peterson]

Did you format the offending cell to wraptext?

If yes, then that square isn't the linefeed. (It's not the carriage return that
forces new lines within the cell--it's the linefeed character (char(10) is the
LF, char(13) is the CR).

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.)