Focal Point
Plus/Minus (+/-) and Excel

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/2501067452

October 30, 2007, 09:43 AM
mitch goudy
Plus/Minus (+/-) and Excel
I am having trouble with an output of a report only in excel format. The problem is with part numbers with the characters "+/-" within the part number field. Outputs in HTML and PDF show correctly, but in MS EXCEL 2000, the "+/-" are not there. Example:
part number IH-10-100UH+/-10PCT is listed as IH-10-100UH10PCT.

WF 7.1.6
Anyone have any experience with this or have any sugguestions?


Mitch
prod/test WF 7.6.9 WIN
October 30, 2007, 03:26 PM
Francis Mariani
quote:
IH-10-100UH+/-10PCT


I can confirm this happens in 5.3.2 as well.

By experimenting with a few variations of the value, I've found that the following work:

IH-10-100UH+ /-10PCT
IH-10-100UH+/ -10PCT
IH-10-100UH+|-10PCT


So it looks like the slash character in conjunction with a + or a - (no blanks in between) causes the problem. If it's acceptable to change the / for some other character, then I would suggest using the CTRAN or STRREP functions to modify the value.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
October 30, 2007, 03:29 PM
Francis Mariani
This works in Windows, use the plus/minus sign:

IH-10-100UH±10PCT



Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
October 30, 2007, 04:07 PM
mgrackin
Just out of curiousity, is this a WebFOCUS issue creating a bad XLS file or is this a problem with Excel not being able to display the values with +/- in them? When you select a cell in Excel which should have this symbol in the value, does it show in the value in the formula bar?


Thanks!

Mickey

FOCUS/WebFOCUS 1990 - 2011
October 30, 2007, 04:21 PM
Francis Mariani
It doesn't show when it's a value in a WebFOCUS report row. If you paste the value into a cell, it shows.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
October 30, 2007, 04:23 PM
mitch goudy
Mickey,
The Excel report looks fine, it is just the cell that should contain the "+/-" in the part number, it is just not there. Example: IH-10-100UH+/-10PCT is listed as IH-10-100UH10PCT; no extra space or anything unusual is seen in the formula bar. Also, I could not find any info on the MS support page for this problem in Excel.


Mitch
prod/test WF 7.6.9 WIN
October 30, 2007, 04:35 PM
Prarie
I tried it 7.6 and it worked.


In Focus since 1993. WebFOCUS 7.7.03 Win 2003
October 31, 2007, 09:43 AM
PBrightwell
I was surprised that that this did not work in 5.3.2 even when double quotes where placed around the part number or when quotes where placed around the slash. But +\- does work. If your users will accept a backslash on Excel then I would define the following;


PART_NO/A25=IF &OUTPUT EQ 'EXL2K' THEN
CTRAN(25, PART_NUM, 47, 92, 'A25') ELSE PART_NUM;

Your other options seem to be to put a space before or after the slash or not allow Excel as an output. By the way EXL97 removes the '+/-' as well.


Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
October 31, 2007, 09:55 AM
mitch goudy
Thanks all for your sugguestions. I was able to use the STRREP and then a CTRAN to replace +/- with ±. When I tried to STRREP with just '±' Excel tranlated to 'ñ' for some reason. So I STRREP +/- to ~ then CTRAN ~ to ± (using ASCII 177).


Mitch
prod/test WF 7.6.9 WIN