|
Converting Text to Number & Summing Issue
I currently run an online application which allows users to export their data
in excel format. This is achieved by creating what is essentially a html file and changing the MIME type to excel. This has work great for the last year or so, however we've come accross an issue with it as of late. For some reason on a worksheet of 2K plus rows, the sum() function doesn't seem to operate as expected. On closer inspection, Excel 2003 allows me to 'convert text to number'. On doing so the correct sum values are reached. An example may be as follows: 288213 24/07/2006 13:20:51 0 Account OCLT002 + 2-OC 123 Customer Large Plumber/Heating A Saunlerson Sales p Phone South and West Richard Theath n No Further Action x Not Re-arranged 288214 24/07/2006 13:21:21 0 Account RDNA027 Blah Blah Customer Self Build A Saunlerson Sales p Phone South and West Richard Pheath n No Further Action x Not Re-arranged The first row does not require the conversion on the fourth column, whereas the second does. Any ideas would be much appreciated! -- ============= VB .NET Developer http://www.rocketscience.uk.com |
Converting Text to Number & Summing Issue
I can't say why, but it is not an uncommon situation.
-- Regards, Tom Ogilvy "JumpingMattFlash" wrote: I currently run an online application which allows users to export their data in excel format. This is achieved by creating what is essentially a html file and changing the MIME type to excel. This has work great for the last year or so, however we've come accross an issue with it as of late. For some reason on a worksheet of 2K plus rows, the sum() function doesn't seem to operate as expected. On closer inspection, Excel 2003 allows me to 'convert text to number'. On doing so the correct sum values are reached. An example may be as follows: 288213 24/07/2006 13:20:51 0 Account OCLT002 + 2-OC 123 Customer Large Plumber/Heating A Saunlerson Sales p Phone South and West Richard Theath n No Further Action x Not Re-arranged 288214 24/07/2006 13:21:21 0 Account RDNA027 Blah Blah Customer Self Build A Saunlerson Sales p Phone South and West Richard Pheath n No Further Action x Not Re-arranged The first row does not require the conversion on the fourth column, whereas the second does. Any ideas would be much appreciated! -- ============= VB .NET Developer http://www.rocketscience.uk.com |
Converting Text to Number & Summing Issue
I'm not sure it will work but give it a try: (my test went ok, but it's probably the "mixed case" that gives problems, .. a text file file named html_import.xls is built of PLAIN html, BUT the html tag looks like: <html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40" Then you can force TD tag to hold numbers. with x:num as xml specifier <TD x:num123</TD HTH, let me know! -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam JumpingMattFlash wrote in I currently run an online application which allows users to export their data in excel format. This is achieved by creating what is essentially a html file and changing the MIME type to excel. This has work great for the last year or so, however we've come accross an issue with it as of late. For some reason on a worksheet of 2K plus rows, the sum() function doesn't seem to operate as expected. On closer inspection, Excel 2003 allows me to 'convert text to number'. On doing so the correct sum values are reached. An example may be as follows: 288213 24/07/2006 13:20:51 0 Account OCLT002 + 2-OC 123 Customer Large Plumber/Heating A Saunlerson Sales p Phone South and West Richard Theath n No Further Action x Not Re-arranged 288214 24/07/2006 13:21:21 0 Account RDNA027 Blah Blah Customer Self Build A Saunlerson Sales p Phone South and West Richard Pheath n No Further Action x Not Re-arranged The first row does not require the conversion on the fourth column, whereas the second does. Any ideas would be much appreciated! |
| All times are GMT +1. The time now is 12:30 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com