1 Attachment(s)
[RESOLVED] Carriage returns and Text Format
Hey,
I am populating a spreadshhet from a database query but am having problems making the text look correct.
In the 3rd column you can see that whilst the text in the cells starts a new line it also appears to have "|" or a symilar symbol, what would be the best way of getting rid of this.
In addition some lines just appear as "#####" which I also dont know why.
I would also like to have a button which allows the user to decide whether the text is displayed along one line or if the text in a cell goes over more and the cell expands.
Any help on the following would be appreciated.
Re: Carriage returns and Text Format
Can you upload a sample of the excel file for a quick resolution?
Re: Carriage returns and Text Format
Quote:
In addition some lines just appear as "#####" which I also dont know why.
generally a number longer than will display in the cell
the | is probably a vbcr (chr(13))
so you should replace vbcrlf with vblf (chr(10))
Re: Carriage returns and Text Format
Attached a workbook with the problem.
Is it worth mentioning that as the data is put into a record set before drwan onto the workbook, it may be possible to apply a fix either bofore or after the data is being displayed.
Re: Carriage returns and Text Format
workbook missing...
You might have to zip it and then attach it...
Re: Carriage returns and Text Format
1. Line-feed character:
Excel use Chr(10) = vbLf to break lines in cells. Your database such as Access uses vbCrLf = Chr(13) & Chr(10) for line-breaks.
The "bar" (with font size < 8) or a "square box" (with font size >=8) represents Chr(13) = vbCr.
* You have to remove Chr(13) in data from database before populating it to Excel.
sFieldText = Replace(sFieldText, vbCr, "")
or
sFieldText = Replace(sFieldText, vbCrLf, vbLf)
or
* After populating use this worksheet formula: =SUBSTITUTE(D2,CHAR(13),"")
2. The text is displayed as ####################################:
When a cell has been formatted as Text (with NumberFormat = "@"), if the cell contains more than 255 characters then it will be displayed like that even you make the cell very wide and/or very high.
* To fix that: Change the "NumberFormat" of the cell to "General".
Re: Carriage returns and Text Format
Quote:
Originally Posted by
anhn
1. Line-feed character:
Excel use Chr(10) = vbLf to break lines in cells. Your database such as Access uses vbCrLf = Chr(13) & Chr(10) for line-breaks.
The "bar" (with font size < 8) or a "square box" (with font size >=8) represents Chr(13) = vbCr.
* You have to remove Chr(13) in data from database before populating it to Excel.
sFieldText = Replace(sFieldText, vbCr, "")
or
sFieldText = Replace(sFieldText, vbCrLf, vbLf)
or
* After populating use this worksheet formula: =SUBSTITUTE(D2,CHAR(13),"")
2. The text is displayed as ####################################:
When a cell has been formatted as Text (with NumberFormat = "@"), if the cell contains more than 255 characters then it will be displayed like that even you make the cell very wide and/or very high.
* To fix that: Change the "NumberFormat" of the cell to "General".
Thanks,
as this is for a single report I have adapted your idea to the SQL layer as below, this has removed the funny lines.
I also changed the Numberformat to General to remove the hashes.
Now I just need to provide a button which can change the sheet or range to wraptext.
REPLACE(Common.tblAudit.[parameters],Char(13),'')