 |
|||||
|
As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Join the TIBCO Community
Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community. | |||||
Focal Point 
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [CLOSED] Convert number value in text field to number but still keep text value
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [CLOSED] Convert number value in text field to number but still keep text value Read-Only TopicGo | Search | Notify | Tools |
| [CLOSED] Convert number value in text field to number but still keep text value | Login/Join |
| Platinum Member |
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, WebFOCUS 7.7.03 Windows, All Outputs | ||
|
| Master |
Are you exporting this from SQL Server? HallwayProd: 8202M1 Test: 8202M4 Repository:  OS:  Outputs:     | |||
|
| Platinum Member |
My database is Oracle. WebFOCUS 7.7.03 Windows, All Outputs | |||
|
| Master |
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. HallwayProd: 8202M1 Test: 8202M4 Repository: OS: Outputs: | |||
|
| Virtuoso |
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. WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007 | |||
|
| Platinum Member |
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. WebFOCUS 7.7.03 Windows, All Outputs | |||
|
| Platinum Member |
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. JC WebFOCUS Dev Studio / App Studio 8.2.01 Windows 7 | |||
|
| Platinum Member |
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". WebFOCUS 7.7.03 Windows, All Outputs | |||
|
| Master |
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.) WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP | |||
|
| Platinum Member |
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. JC WebFOCUS Dev Studio / App Studio 8.2.01 Windows 7 | |||
|
| Platinum Member |
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. WebFOCUS 7.7.03 Windows, All Outputs | |||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only TopicFocal Point Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [CLOSED] Convert number value in text field to number but still keep text value
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [CLOSED] Convert number value in text field to number but still keep text valueCopyright © 1996-2020 Information Builders
| View $GS_USERNAME's Public Profile |
| Add $GS_USERNAME to my Ignore List |
| View Recent Posts by $GS_USERNAME |
| Notify me of New Posts by $GS_USERNAME |