LF displays as square box in cell

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?

0
2/17/2011 4:20:38 PM
excel 39879 articles. 2 followers. Follow

13 Replies
944 Views

Similar Articles

[PageSpeed] 41

On Thu, 17 Feb 2011 08:20:38 -0800 (PST), John <jck.office@gmail.com> wrote:

>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.
0
ron6368 (329)
2/17/2011 4:49:25 PM
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.



On 02/17/2011 10:20, John wrote:
> 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?
>

-- 
Dave Peterson
0
petersod1 (224)
2/17/2011 11:46:03 PM
> Is this behavior, for a given cell, dependent on the platform (e.g. Mac v=
s Windows)? =A0If so, one way to clean it up might be to run a macro that r=
eplaces 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.
0
2/18/2011 12:23:11 AM
On Feb 17, 4:46=A0pm, Dave Peterson <peter...@XSPAMverizon.net> wrote:
> 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 cha=
racter:
> =3DCODE(MID(A1,5,1))
>
> This formula returned 10. =A0It 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.
0
2/18/2011 12:29:18 AM
vbOn Thu, 17 Feb 2011 16:23:11 -0800 (PST), John <jck.office@gmail.com> wrote:

>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.
0
ron6368 (329)
2/18/2011 12:31:49 AM
> 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) 

0
2/18/2011 2:34:05 AM
On Thu, 17 Feb 2011 21:34:05 -0500, "Rick Rothstein" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote:

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

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.
0
ron6368 (329)
2/18/2011 3:35:41 AM
>> 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. 

0
2/18/2011 3:43:21 AM
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>

-- 
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc


0
gs3102 (378)
2/18/2011 3:58:25 AM
On Thu, 17 Feb 2011 22:43:21 -0500, "Rick Rothstein" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote:

>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.
0
ron6368 (329)
2/18/2011 4:24:30 AM
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?

On 02/17/2011 18:29, John wrote:
> On Feb 17, 4:46 pm, Dave Peterson<peter...@XSPAMverizon.net>  wrote:
>> 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.

-- 
Dave Peterson
0
petersod1 (224)
2/18/2011 12:37:27 PM
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" <gs@somewhere.net>
wrote in message
news:ijkqp9$fvh$1@news.eternal-september.org...
> 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>
>
> -- 
> Garry


0
2/18/2011 2:13:13 PM
On Feb 17, 9:20=A0am, John <jck.off...@gmail.com> wrote:
> 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.
0
2/20/2011 4:22:12 AM
Reply:

Similar Artilces:

How can I display and print more than 1024 characters in a cell?
I have Excel 2003 and would like to print with more than 1024 characters in a cell. Currently any characters beyond the 1024 characters are not displayed under the 'wrap text' function. Thank-you Hi Lisa If you use Alt+Enter at intervals within the text that you are typing within a cell, this will force a line feed. Not only does this make large amounts of text more readable, it will also permit you to display more than 1024 characters. -- Regards Roger Govier "Lisa" <Lisa@discussions.microsoft.com> wrote in message news:AB2425B9-A2E6-4907-AB6C-95856B424153@...

Combo box returning wrong value
I am having trouble with a combo box, which is filled by a query to that displays two fields (Fund # and Fund description). The fields are unbound and I am using VBA to store the unbound fields in a table. I am using an option box to determine which sequence to use to present the data (I use two different queries and store one of them in the row source of the combo box, depending on the option box value). This all works fine. The problem comes when an item is selected. I would like to store the values both the Fund # and the Fund value in my table with VBA. My VBA code in the co...

GROW BOX
I want to know about Grow Box. i.e, in a report one field may have more data than others. Then in one record size of the box will not be equal for all. I saw one sample in file explaining this situation. But I couldn't understand. In that exmple, in the query design they have created a blank field using the function - StringBline(StringCnt([Test],Chr(13))+(Int((Len([Text])/46+0.5))+1). I copy and modify this function in my database. But it's not working. It says Function StringBline is not defined, and even I coudn't get help on this function in MS Access. Could please...

Bare LF:s in mail...
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: Exchange I have problems in sending mail to certain receivers. I get tha message that the mail was stopped because it contained bare LF:s. <br> This is very disturbing. how do I fix this? GoranS@officeformac.com wrote: > I have problems in sending mail to certain receivers. I get tha > message that the mail was stopped because it contained bare LF:s. > This is very disturbing. how do I fix this? This is not normal message. It is coming from your Exchange Server, not En...

CR/LF in cells
Hello all. I need to be able to put multiple lines of text with blank lines between them into cells. I can do this by creating the data as I want it in a word file then copy/paste into the cell, but I would like to be able to eliminate this step and enter directly. When I enter the first line of data and hit the return key, it goes to the next cell in the column. Any ideas? I figure there has to be a way to turn it on since it is possible to paste the same data in the cell. Thanks in advance, Maury Found it. I swear, work on something all day, then figure it out right after asking for he...

display answers to a questionnaire in a chart
A friend has a 12 question survey with a number of different answers available. How do I display the different responses to the 12 questions in a single chart? What type of answers? Text strings like yes or no or similar? I think you would have to give the answers a number value then create = your chart from those numbers. Use VLOOKUP on a table of answers and numbers to retrieve the number associated with a particular answer. Gord Dibben MS Excel MVP On Wed, 2 Jun 2010 04:48:01 -0700, Joanne Bryan <Joanne Bryan@discussions.microsoft.com> wrote: >A fr...

XML page cannot be displayed on Version 10
Hi; my user who need access to other's user REPORTS.DIC for generating a report, in order to let her easy access to another user reports.dic, I copy this REPORTS.DIC to a folder on her local computer and change the path on her Dynamics.set file. Because I want to keep her original reports.dic. It was workable under this change when I use GP 8.5. However; when I load GP 10, I found the following message when I click on each module like Home, Financial, etc. The XML page cannot be displayed Cannot view XML input using XSL style sheet. Please correct the error and the click the R...

Drop Down Box #7
Greetings, I have an Excel doc with drop down boxes from the control toolbox. I was able to affect the properties of these boxes by right clicking on them and going ino the dialogue box for formatting the controls. Now when I try to do it, it doesn't give me the right click menu. Is there some method in Excel to reactivate the right click menu on these controls? Perhaps some sort of template mode? Thank you. Rod You can try this: Press Alt+F11 Press Ctrl+G Type this into the pane that appears at the bottom of the Window and press Enter: Application.CommandBars("Cell")...

Formula displays Error rather than message
I have the following formula in the Control Source of a report; the problem is rather than "No Changes", it displays Error when this evaluates to true: =IIf([RCD_NW]="Y","New Position",IIf([RCD_NW]="N","Change to Existing","No Changes")) Can some one please help me out with this? How can I get the third message to display when RCD_NW is neither "Y" nor "N"? Thanks in advance. Does the control always display Error? If so, you may need to change the name of the control to something like txtRCD_NW. You might...

Print Dialog box
Is it possible in excel to create a box which will pop up when you click on a cell so that you can type in a code and all details for that specfic code get printed out Maybe... I would think that one of the easiest ways to implement the "box" would be to use data|validation (in the Data|Validation option in xl2003 menus). The list would be the leftmost column on a separate worksheet (nicely named!) and the other info would be in columns B:xx. Then you could use =vlookup()'s to return the associated values for that chosen option. Debra Dalgleish has some notes...

OWA: No items to be displayed
Hello, I'm searching for hours what might be the problem. Untill now no avail... All my users can access OWA. Now we have some users (managers) who can access almost every mailbox. Locally with Outlook this works fine! But with OWA for some users they get a screen where there is no navigation tree. The get the screen that is normally on the right side of the navigation tree, and there is displayed: "There are no items that can be displayed in this view". Changing the view doesn't change anything. BUT, when the manager types for example: https://exemple.com/exchange/pe...

HELP! i need to add in a cell without automatically deleting
i need to add text into cells that have pre-exisiting data already in i and every time i click on the cell to add something in at the end i automatically deletes the pre-exisiting data. so i have to type all th data over again and then add in what i wanted to at the end...how can add something to my cell without i automatically deleting the cell? -- chloe77 ----------------------------------------------------------------------- chloe777's Profile: http://www.officehelp.in/member.php?userid=450 View this thread: http://www.officehelp.in/showthread.php?t=121236 Posted from - http://www.o...

Relationship Default View
It appears that Realtionship roles between Accounts, Contacts and Opportunities can be modified capture additional information concerninng the realationship, but there does not appaer to be a methodology for modifying the default view or form to capture and dispaly this information ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsr...

Reminders display order
When the reminders are displayed in Outlook 2003 the appear to be in no order. Is there a way of displaying the reminders in Due in order? Not with the built-in reminder feature, but there are some third party reminder programs at www.slovaktech.com, at least one of which (Reminder Manager) allows sorting on any column. -- Jocelyn Fiorello MVP - Outlook *** Messages sent to my e-mail address will NOT be answered -- please reply only to the newsgroup to preserve the message thread. *** "robin9876@hotmail.com" wrote: > When the reminders are displayed in Outlook 2003 the ...

Axis display as category
I am desiring to display a chart with the X Axis being cards from a deck of playing cards and Y Axis as number of times receiving these in a game. However Excel chart insists on displaying my Ah, Kh, etc as a number 1 - 52 instead of the actual text in the column. All of the radio button selections to format this to "category" are grayed out..ARGH! How would one force Excel to display the actual text in the column instead of counting numbers? TIA Forgot to mention I want it as a XY scatter graph. Bar charts display as text as is desired. "doco" <perdedor@...

Cell formats #3
Is it possible to customise a cell in excel that will display a negative number within parenthesis rather than the entered value preceeded by a minus sign? e.g Value entry "-1500" displays as "-1500" Required display "(1500)" Regards Steve Steve, Yes. Format - Cells - Number. In Category, choose Number or Currency, and you'll have a box to choose the display format of negative numbers. For more control, see Custom. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "steve" <anonymo...

Display Bitmap using GDI+
Hi all, I have a MDI application in which I want to display bitmap in view ( Base class is CScrollView) with Scroll bar using GDI+, later I want to mark some part of the image. So plz tell me how I can draw a bitmap with Scroll bar. Thanks and Regards Rinu Use GetScrollCtrl to get scrollbar window and subclass window to intercepting message. in intercepted handler, process WM_PAINT with drawing bitmap image.. -- WebSite : Realization of Dream { imagine your dream} - http://rodream.net WebMail : rodream@naver.com "Rinu Gopalakrishna Pillai" wrote: > Hi all, ...

Square screen resolution on PocketPC
Hello, I have a question about square screen resolution on PPC, precisely how can I determine that my PPC is using high-resolution or low-resolution. For portrait/landscape mode I have use something like that. if (GetDeviceCaps(hdc, LOGPIXELSX) < 192) // low-res else // hi-res I'm not sure that this will work with square-ppc. I can't find any info about that. Any help will be greatly appreciated. ...

Save As Dialog Box Corrupts Header and Footer in Word 2007
Why does the Save As dialog box corrupt the header and footer? 1. I have a file open that includes header and footer text. 2. I select Save As from the file menu and I notice my header and footer text is replaced with generic-like entries that resemble Word templates. 3. When I click the OK button, the file that is saved has these corrupted header/footers. What is going on here and how do I fix it? This is Word 2007 on a Windows PC. Bob Why have you posted this again? -- Terry Farrell - MSWord MVP "Bob Vendryes" <BobVendryes@discussions.microsoft.com&g...

invalid data encoding [LF.CRLF]
I have a SBS2003 server with Exchange. I have set up one of the user email to also forward email to her external (comcast) address. I did this by creating a "contact" with her external email and then forwarding emails to her internal account to the contact address. Her interal email works fine in getting emails, however, users are getting the error message: -------------- There was a SMTP communication problem with the recipient's email server. Please contact your system administrator. <mail.xxxx.com #5.5.0 smtp;554 invalid data encoding [LF.CRLF]> ------...

How can I display a cell range in a text box in excel?
I want to display the contents of a cell range in a text box. I know how to put contents from one cell in a text box but I need to put the contents of 24 cells in my text box. Or how can I change the length and height of cells below other cells without changing the other cells length and height. Is there a way to put a line to freeze panes to change the length and height of cells below the line? Would the '&' work for this problem. eg if A1 = "Pat" and B1 = "Hughes" then the formula A1&B1 would return PatHughes, formula A1&" "&...

Combo Box Result
Hi all, I have two unbound combo boxes (c1 and c2) that based on the value of c1, will determine what shows for c2. By selecting c1, it will correctly limit to what c2 shows, but when I select the option for c2, it is not choosing the correct value. The result set for c2 is based off a table, that has the following format: ISID ISD ITID 1 red 1 2 blue 1 3 green 1 4 red 2 5 blue 2 6 green 2 c1 will select the ITID, which then limits what I see in c2 to the correct set (I even brought in the ISID column to make sure the numbers ...

How can I display a value from a spreadsheet cell in a column in a document library?
I want to store expence sheets in a document library and be able to view the total on each spreadsheet document in a column in the document library. Does anyone know how to do this? Regards Roger Eriksen The suggestions in the following Sharepoint newsgroup thread may help you: http://groups.google.com/groups?&threadm=%23dKZiKyVFHA.928%40TK2MSFTNGP15.phx.gbl Roger Eriksen wrote: > I want to store expence sheets in a document library and be able to view the > total on each spreadsheet document in a column in the document library. > Does anyone know how to do this? > &...

Losing CR/LF
If this is not right place to ask this type of question my apologise and please point me to the appropiate place. So of our product descriptions have data enter as: Product descption line etc etc... More product descrption taking up several lines... 1.blah blah 2.blah blah This displays properly in CRM and when using the data in a Crystal report. However I'm trying to use SQL Reporting and the carriage return line feeds are being stripped and it's showing on the report as a single line. Anyway around this? Thanks in advance John Just bumping this to see if anyone has any...

In-Box
My in-box label in left rail shows 4 messages, however they do not download, while at the same time other new messages download. Has Outlook forgotten how to count? You probably have a filter applied to your view so that some messages are not shown. Verify that your view is set to the Messages view via the View-> Current View menu If that doesn't work either, use Customize Current View from the same menu and use the Reset Current View button. -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, H...