Conditional number formatting in Excel export

[Bloke152]

Hi all,

I have a Crystal report which has values which could be number or string.
The aim is to have them formatted as number if they are numbers and string if they are not - once they are exported to excel.

I have tried writing a conditional formula but since they are both going to live in the same field it does not like it.

I have also tried having two formula fields occupying the same space, one showing the number and supressed if it is not a number. The other showing the string and suppressed if it is a number.
However, this screws up the column alignment when exported to excel, its a shame as this does work!

Does anyone have anything other ideas of things to try?

 

[kray4660]

It seams the second option should work provide the two formula fields are exactly the same size and in the same position. I will check on my end. Of course, I am not sure how Excel will handle a mix of numbers and text.

I tested the concept on my end and it worked just fine.

 

[Bloke152]

Thanks,

It appears as though this does work. However, it appears as though it still struggles with export - Data only.
Its not an issue, I have spoken to the user and they wont be using that option anyway.

 

[SkipVought]

Bloke152,

I'm not a CR guy but am Excel.

Can you clarify for me the issue regarding the text vs number in the same column, which Excel does not handle well: will you do arithmetic on these numeric values?

If not, you ought to store them as TEXT, that is TEXT DIGITS.
FAQ68-6659.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein