Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Conditional number formatting in Excel export

Status
Not open for further replies.

Bloke152

Programmer
Aug 9, 2005
123
NO
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?
 
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.
 
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.
 
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]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top