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 Web Intelligence (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 WebI) =Replace (Replace([Trans Desc] ;Char(34) ;" ") ;Char(39);" ")

The nested replace functions change the double and single quotes to 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.