As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.
Join the TIBCO Community TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.
From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
Request access to the private WebFOCUS User Group (login required) to network with fellow members.
Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.
Help. We are outputing a report in Excel format. We have 3 fields... Name, ID, Amount Field Name and ID are alphanumeric and Amount is numeric. But if ID is only numbers then we get it converted to numeric. The problem is the ID is so long that when it displays it is showing 1.234E+15 instead of 1234567890123450. I know we can instruct them to format that column in excel to text and it works. But we want very little user training. Has anyone found an answer?
We have the same issue, and the only work around we've found for it is to change the source field to alpha by putting an alpha character into the front of it. This way, excel never thinks its a number. We've not tried making that introductory alpha character an apostrophe, tho. You might try that; then excel might react perfectly and your users would never know the difference. Actually, i'm going to go try that myself...
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003
This is a known bug in Excel that Microsoft refuses to fix. They think all numeric values, even if they are inside quotes indicating text, should be treated as if they are numbers.
There are 2 solutions:
1) Switch to StarOffice (or the free OpenOffice, my personal preference) 2) For those who can not or will not switch, put a non-numeric character in the string of characters. I do this by prepending an underscore, so 11123453 would be _11123453.
Do you ever format the ID number for readability, given that it is so long? Adding '-' and spaces makeS it easier to read and Excel treats it as text ... like formatting an SSN.
Posts: 346 | Location: Melbourne Australia | Registered: April 15, 2003
I have a similar situation with numeric data that is not being operated on arithmetically but XL2K changes it to floating point. To get around that, the data were changed to alpha; PTOA in this case.
Posts: 252 | Location: USA | Registered: April 15, 2003
I think a simpler solution would be to concatinate a single quote mark to the front of the ID string. Excel will ignore this single quote as this is its internal way of representing numeric strings as alpha. (Left over from the 123 days). I.e.
NEWID/A21 = '''' | ID;
Posts: 44 | Location: New York City | Registered: May 23, 2004
Hi, I didn't want to append a character that would be visible in the output, like quote or pound sign. And of course if you try to append a space, Excel will promptly remove it.
I found appending an ascii character worked well. 'alt 032'|ClaimNumber. It will look like a space in the fex but Excel respects it as a character and will treat the field as alpha instead of changing it to numberic.
My apologies. That last post was grossly incorrect!! I tried the ascii character and it DIDN'T work. My unfortunate final solution is appending a # sign to the fields.
I don't know which version of Excel you are using, but I am quite certain that the following string entered into a cell will NOT display the leading quote mark: '01234. It will show you 01234.
It must be a single quote, and you may not close it, in other words, just the leading position. This has been the common way to tell Excel that the cell content is to be formatted and displayed as a text field, and not as a number. (the preserves leading zeroes and will not put a commma between the thousands, etc.)
Posts: 44 | Location: New York City | Registered: May 23, 2004
ZUT ALORS! Prepending an apostrophe very definitely doesn't work. go figure! It does in fact bring down the apos as a readable character, just as Steven and Lisa say. If your users have excelxp (do they??), then a number formatted as alpha comes down left adjusted if you've been careful to define it as an /An field. The cell format isn't TEXT, but it does have that little green flag and the information icon added that says that the field is 'number stored as text'. So you should be ok if your initial definition is tight. You can try formatting your field as /AnL so that it will pack out all shorter numbers with leading zeros, which will keep your formatting tight.
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003
Focal Point Forums 