LF displays as square box in cell

[John]

I have some cells with text where the LF (line feed) displays as a
square box. Searching the web for this scenario suggests that turning
on Word Wrap should eliminate this display but it does not in my case.
Also, not all LFs in the cell display as a square box and one of my co-
workers does not see the behaviour when he opens the file on his PC
(another co-worker does see the problem).

The text has been downloaded from a SQL query and I'm using Excel 2007/
WinXP.

Any suggestions on how to clean up the view of the cells with this
text?

 

[Ron Rosenfeld]

I have some cells with text where the LF (line feed) displays as a
square box. Searching the web for this scenario suggests that turning
on Word Wrap should eliminate this display but it does not in my case.
Also, not all LFs in the cell display as a square box and one of my co-
workers does not see the behaviour when he opens the file on his PC
(another co-worker does see the problem).

The text has been downloaded from a SQL query and I'm using Excel 2007/
WinXP.

Any suggestions on how to clean up the view of the cells with this
text?

Is this behavior, for a given cell, dependent on the platform (e.g. Mac vs Windows)? If so, one way to clean it up might be to run a macro that replaces vbCr and vbLf with vbNewLine, or something similar.

 

[Dave Peterson]

If you have wraptext turned on, then I bet that square box is not a line feed.

You could use a formula like this to determine the ASCII code for the character:
=CODE(MID(A1,5,1))

This formula returned 10. It was the 5th character in A1 in my testing.

 

[John]

Is this behavior, for a given cell, dependent on the platform (e.g. Mac vs Windows)?  If so, one way to clean it up might be to run a macro that replaces vbCr and vbLf with vbNewLine, or something similar.

Ron,

Thank you for the reply.

I have no access to a Mac so I am unable to check that option.

The troublesome cell(s) also did contain a CR character(s) and I did
use a macro to successfully replace those (I did not mention that in
my original post since they were not an issue.)

I am not aware of VbNewLine and I will look into that. What is
different between vbNewLine and vbLf?

I have to be careful though, I cannot do a global replace in the cell
because in the example I remember (sorry not at work machine now) I
have 3 or 4 legitmate LF and only one appears as a square box.

 

[John]

If you have wraptext turned on, then I bet that square box is not a line feed.

You could use a formula like this to determine the ASCII code for the character:
=CODE(MID(A1,5,1))

This formula returned 10.  It was the 5th character in A1 in my testing..

Dave,

Thank you for the reply!

I used the exact formula you noted to examine every character in the
cell and all the characters were either printable ASCII codes or code
10 (LF).

I'm stumped at this moment.

I'll keep poking when I have a moment, maybe I'm just missing
something fairly obvious but my two co-workers have not seen anything
yet either.

 

[Ron Rosenfeld]

I am not aware of VbNewLine and I will look into that. What is
different between vbNewLine and vbLf?

vbNewLine inserts a Chr(10) or Chr(13) depending on the OS. But since you don't have Mac's that is probably not the explanation for what you are seeing.

 

[Rick Rothstein]

vbNewLine inserts a Chr(10) or Chr(13) depending on the OS.

Actually, for a Microsoft OS, vbNewLine returns the vbCrLf... a Carriage
Return followed by a Line Feed; or, using the Chr function, it returns
Chr(13)&Chr(10)... it returns vbCr... Chr(13)... for a Mac.

Rick Rothstein (MVP - Excel)

 

[Ron Rosenfeld]

Actually, for a Microsoft OS, vbNewLine returns the vbCrLf... a Carriage
Return followed by a Line Feed; or, using the Chr function, it returns
Chr(13)&Chr(10)... it returns vbCr... Chr(13)... for a Mac.

Rick Rothstein (MVP - Excel)

Exactly. I meant my OR to be inclusive, and there is discussion amongst grammarians whether writing and/or is redundant. I usually don't use and/or, but some do.

 

[Rick Rothstein]

Exactly. I meant my OR to be inclusive

I don't read "or" as being inclusive that way, but no never mind... anyone
reading this thread will know what-is-what now. More important<g>... what I
want to know is why the hell did Microsoft choose to use a double character
sequence to indicate a new line, I mean... two characters, really!!? I
cannot begin to tell you what a pain in the backside that has been across
the years when I used to volunteer answering question in the compiled VB
newsgroups of old.

Rick Rothstein - (MVP - Excel)is discussion amongst grammarians whether
writing and/or is redundant. I usually don't use and/or, but some do.

 

[GS]

John presented the following explanation :

I have some cells with text where the LF (line feed) displays as a
square box. Searching the web for this scenario suggests that turning
on Word Wrap should eliminate this display but it does not in my case.
Also, not all LFs in the cell display as a square box and one of my co-
workers does not see the behaviour when he opens the file on his PC
(another co-worker does see the problem).

The text has been downloaded from a SQL query and I'm using Excel 2007/
WinXP.

Any suggestions on how to clean up the view of the cells with this
text?

I've sometimes seen this behavior when importing single line text from
a file that was authored with WordWrap turned on. Not saying this is
the cause! But since I work with single line data stored in text files
a lot, I've learned to pass my data through a quick filtering function
to remove the unwanted characters. -It's an annoying situation to say
the least.<g>

 

[Ron Rosenfeld]

what I
want to know is why the hell did Microsoft choose to use a double character
sequence to indicate a new line, I mean... two characters, really!!? I
cannot begin to tell you what a pain in the backside that has been across
the years when I used to volunteer answering question in the compiled VB
newsgroups of old.

Maybe "backward compatibility"?

They were probably copying the old printers, where <LF> pushed the paper out one line, and <CR> brought the carriage back to the beginning of the line.

 

[Dave Peterson]

Hmmm.

Just a couple of things to try...

I'd try changing printer (or printer drivers) to see if that helped.

If not, I'd try starting excel in safe mode:

close excel
windows start button|Run
excel /safe

And open your file to see how it reacts.

============
When you enter the data manually, do the alt-enters work nicely or do they show
up as squares?

And are sure wraptext is toggled on? Does the text wrap in the cell -- even for
long text without the alt-enter character?

 

[Jim Cone]

Amen; a good portion of the Excel questions posted in the
newsgroups wouldn't be necessary if users just cleaned
imported data before trying to use it...
Trim, Clean, Unwrap, Substitute and Convert #'s.
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(Permutations: with valid words highlighted)




"GS" <[email protected]>
wrote in message
news:[email protected]...

 

[John]

I have some cells with text where the LF (line feed) displays as a
square box. Searching the web for this scenario suggests that turning
on Word Wrap should eliminate this display but it does not in my case.
Also, not all LFs in the cell display as a square box and one of my co-
workers does not see the behaviour when he opens the file on his PC
(another co-worker does see the problem).

The text has been downloaded from a SQL query and I'm using Excel 2007/
WinXP.

Any suggestions on how to clean up the view of the cells with this
text?

Some additional follow up from some experimenting tonight:

With cell contents of:

<text>
LF
<text>
LF
<text>
LF <ASCII Space>
LF <ASCII Space>

I would still see one square box displayed in the cell (but not in the
fomula bar) and it would be the last LF (none of the other LFs would
display a square box, which is still curious to me). Since the LF
<ASCII Space> was generally meaningless I replaced the string Chr(10)
& Chr(32) with Chr(10) and the display issue goes away. The root cause
is still unknown but at this point it is a don't care.