In database, I have a column which has the format is varchar2, data of this column can be text or number. When I export the report to excel, cells which have data as the number have the green triangle at the top left corner of those cells indicate that they are format as text. Other cells which have data as text don't have this green triangle. Is there anyway that when I export the report to excel, it can recognize these values are number, convert them to number and eliminate these green triangle.

This message has been edited. Last edited by: hainguyen,

Are you exporting this from SQL Server?

My database is Oracle.

I'm not familiar with Oracle. Is there an option to export your data to a .csv file? I use this option in SQL Server and when I open the file in Excel, it converts numbers as text to numbers.

It's the way Excel react with multiple data type in the same column.

In Excel (which as to be set to each computer) go to :
File/Options/Formulas and uncheck : "Numbers formatted as text or preceded by an apostrophe"

It may resolve the case.

Hallway: Look like there is no CSV format. However, I need to export it to Excel for my users.
MartinY: This solution almost solves my issue. There is only one thing, when I do Sum for the range of cells which contains number, it returns 0 but if I do '+' or '-', it run just fine. Don't know why this happen. My excel is 2010.

You may want to do some research on the SUMIFS function in Excel, looks as though you may be able to use that for your sum.

Well, I can't tell my users to use SUMIFS instead of SUM. I think there should be any setting in Excel let me do that. But as my research, SUM will not work with "number formatted as text".

Well, personally I would split up the incoming data into two columns with a define, so the numeric data ends up in one column in Excel and the text in another. Then the users could do the SUM on the numeric data - or you could do it yourself in the report. And if they don't like that approach I'd just invite them to do better themselves (I'm using parliamentary language here .... Internally I'm thinking of something far less polite.)

The only additional question I would pose is do you even need to display the non-numeric data in that column?

If so then the solution George provided is probably going to be your best bet if you want to avoid any additional hassle for the end-user.

If you do not need the non-numeric data to display why not create a view in the Oracle db for your data-set that limits to only numeric data in that column? Then from there you could create your synonym off that view and report off that.

OK, that column's name is RESULT, it will show the results of differest TESTs. Some TEST's results are number : 0.23, 4.3... But others are text : <0.111, YES, NO. And the challenge thing is they will do calculation, SUM... on the result which are the number to do some analyze. There is old application which is not WebFocus can do that when it exports to Excel, I just try to do the best to duplicate that application so we can replace it. But it seems there is still some limit we can do. Maybe, I will just tell them just convert it using Excel. Thanks all for your suggestion. I will close the case here.