Focal Point
When Output to Excel a alpha value ID is converting to Numeric

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

August 31, 2004, 08:10 PM
<Steven James>
When Output to Excel a alpha value ID is converting to Numeric
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?
August 31, 2004, 09:47 PM
susannah
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...
September 01, 2004, 04:33 AM
TerryW
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!
September 01, 2004, 07:03 AM
Piipster
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.
September 01, 2004, 11:41 AM
jimster06
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.
September 01, 2004, 02:12 PM
Steve C
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;
September 09, 2004, 02:48 PM
Lisa Starr
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.
September 10, 2004, 04:37 PM
Lisa Starr
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.
September 10, 2004, 04:55 PM
<Pietro De Santis>
What about the great idea of prepending a single-quote?
September 10, 2004, 07:34 PM
Lisa Starr
I still get the apostrophe showing in the spreadsheet...course that does look better than the # sign. Did anyone else get it to work??
September 13, 2004, 07:23 PM
Steve C
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.)
September 14, 2004, 12:27 PM
Lisa Starr
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?
September 14, 2004, 05:21 PM
susannah
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.