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...

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!

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.

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.

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;

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.

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.

What about the great idea of prepending a single-quote?

I still get the apostrophe showing in the spreadsheet...course that does look better than the # sign. Did anyone else get it to work??

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.)

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?

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.