Topics Map > University of Chicago > IT Services > Business Systems > Reporting Systems
Business Objects - Webi - Problem Copying and Pasting to Excel - Cells are Combined
This article explains how to create a variable to replace quotes in a text object that cause problems with copying and pasting to Excel.
When quotation marks (single or double) are encountered in data being copied and pasted to Excel they can cause the data from later cells to be seen as a single text object and placed into a single cell in the spreadsheet. Removing the quotation marks prevents this issue. This can be done using a formula with the Replace() and Char() functions.
For example when quotation marks are encountered in the Transaction Description object:
(using DeskI) = Replace (Replace(<Trans Desc> ,Char(34) ," ") ,Char(39) ," ")
(using WebI) =Replace (Replace([Trans Desc] ;Char(34) ;" ") ;Char(39);" ")
The nested replace functions change the double and single quotes (using the char(34) and char(39) since the quotes can't be entered directly into the formula) each with a space.
- Replace the space between the quotes with another character to replace the quotes with a different value. Don't use a quote or a character that Excel will see as a math operator (+, -, / or *). You can remove the space then simply remove and quotation marks.
- You can also use the replace function to replace or remove other text by replace the char(nn) with text in double quotes.