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.

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 you encounter single or double quotation marks in data you want to copy and paste from WebI to Excel, they may cause the data in Excel to appear as a single text object in a single cell in the spreadsheet. Remove the quotation marks to prevent this issue. Do this using a formula with the Replace() and Char() functions.

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 into spaces. Use the char(34) to represent double quotes and char(39) to represent single quotes since the system will misinterpret your intention if you type " or '.

Notes

  • 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 the quotation marks.

  • You can also use the replace function to replace or remove other text by replacing the char(nn) with text in double quotes.




Keywords:Business_Objects, IRF, AURA, SDW, Financial, Payroll, Report, Export, GEMS   Doc ID:17316
Owner:James I.Group:University of Chicago
Created:2011-03-14 19:00 CDTUpdated:2017-03-06 11:57 CDT
Sites:University of Chicago, University of Chicago - Sandbox
Feedback:  2   0