I have a calculated field on a baq to get the first 150 characters of the comment field and turn them into a string to get rid of the carriage returns so that when it is copied and pasted into Excel the data stays on one line. My field is string(substring(DMRHead.CommentText,1,150)). When I copy and paste from the BAQ designer all the data stays on one line but when I add the query to a dashboard then copy and paste the data from there into excel it still has the strange breaks onto other lines. Any idea how to fix this?
I’ve run into some weird control characters embedded in a single field’s data. Particularly the RS (Record Separator, Code: 0x1E, 30dec).
When I clean up data that I expect to copy from a table/grid, I always use multiple substitute() calls for
- Tab - CHAR(07) -> " " (3 spaces)
- RS - CHAR(30) -> " " (use at least 1 space to keep words from running together)
- LF - CHAR(10) -> " "
- CR - CHAR(13) -> " "
I’ll usually end with Trim() to clear extraneous trailing spaces.
EDIT: I’ll occasionally look for - and replace - non standard ASCII chars
- Non-breaking spaces - Char(160))
- Em Dashes and Fancy quotes are most often UTF encoded and require special handling