Focal Point Banner


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. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name.


Connect to myibi
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     When Output to Excel a alpha value ID is converting to Numeric

Read-Only Read-Only Topic
Go
Search
Notify
Tools
When Output to Excel a alpha value ID is converting to Numeric
 Login/Join
 
<Steven James>
posted
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?
 
Report This Post
Expert
posted Hide Post
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, 2003Report This Post
Silver Member
posted Hide Post
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.

And thank Microsoft next time you talk to them!
 
Posts: 40 | Registered: March 10, 2004Report This Post
Guru
posted Hide Post
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, 2003Report This Post
Guru
posted Hide Post
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, 2003Report This Post
Silver Member
posted Hide Post
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, 2004Report This Post
Member
posted Hide Post
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.

Lisa.
 
Posts: 14 | Registered: September 02, 2004Report This Post
Member
posted Hide Post
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.
 
Posts: 14 | Registered: September 02, 2004Report This Post
<Pietro De Santis>
posted
What about the great idea of prepending a single-quote?
 
Report This Post
Member
posted Hide Post
I still get the apostrophe showing in the spreadsheet...course that does look better than the # sign. Did anyone else get it to work??
 
Posts: 14 | Registered: September 02, 2004Report This Post
Silver Member
posted Hide Post
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, 2004Report This Post
Member
posted Hide Post
I have excel 97. When I redefine the field as follows: '''|claimnumber

I get: (FOC255) COMPUTATIONAL ELEMENT IS TOO LONG: '''|ClaimNumber;

If I redefine as: ''''|claimnumber

I get:
'001000000007549 in the spreadsheet.

A side excel question....when I put the output to spreadsheet, it includes stuff in cell A1 that I don't want. Looks like:

Sheet1 False False False 5520 13260 240 105 False False

Anyone know how I can get this to go away?
 
Posts: 14 | Registered: September 02, 2004Report This Post
Expert
posted Hide Post
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, 2003Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     When Output to Excel a alpha value ID is converting to Numeric

Copyright © 1996-2020 Information Builders