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

Can you upload a sample of the excel file for a quick resolution?

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

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.

workbook missing...

You might have to zip it and then attach it...

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

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