1

I have a PHP script which exports data from database to different formats, including as an Excel document. If a text field contains such a value as 123123123123, Excel, by default converts it to 1.23123E+11. Is it possible to prevent this absolutely unnecessary behaviour?

Ry-
  • 218,210
  • 55
  • 464
  • 476
Jacobian
  • 10,122
  • 29
  • 128
  • 221
  • Can you show the exporting code, please? – Ry- Sep 15 '13 at 20:11
  • This is off topic because it's about Excel, not programming. Try [su]. – tckmn Sep 15 '13 at 20:12
  • I'm guessing that your "Excel document" is actually a comma-separated value file pretending to be an Excel .xls file... if you're creating a real Excel file, then what library are you using? – Mark Baker Sep 15 '13 at 20:13
  • There are thousands of forum threads under the tag `excel`. And besides this question is no less about programming than about excel itself. Because the problem, I guess, can be solved on the part of PHP if I could specifically render values exporting to excel file. – Jacobian Sep 15 '13 at 20:15
  • Then perhaps you'd tell us how you're creating your Excel file – Mark Baker Sep 15 '13 at 20:15
  • @Mark Baker Yes, in fact it is not a true Excel file. It's a hand-made file with a number of appropriate headers which pretends to be excel. – Jacobian Sep 15 '13 at 20:16
  • Then perhaps switching to one of the many libraries that can generate real Excel files from PHP scripts will help, because they'll let you set a number format mask for cell values... the list [here](http://stackoverflow.com/questions/3930975/alternative-for-php-excel) should give you a few options – Mark Baker Sep 15 '13 at 20:17
  • I'm not ready to use a library yet, because there is some difficult business logic and on the whole it works well, except this one tiny issue with formatting. – Jacobian Sep 15 '13 at 20:20
  • In that case, try wrapping those longer values in quotes so that they're treated as strings – Mark Baker Sep 15 '13 at 20:21
  • @minitech The sript is too large and contains some sort of spaghetti code now. – Jacobian Sep 15 '13 at 20:22

1 Answers1

4

If your goal is to store the number as text (and not do math on it later) and the PHP is exporting as a CSV, a simple solution would be to export the number as a CONCATENATE function.

=CONCATENATE(123123123123) renders as 123123123123 in text format when the CSV is opened in Excel (this is for Excel 2010).

Ry-
  • 218,210
  • 55
  • 464
  • 476
Excel Tactics
  • 281
  • 1
  • 6
  • Thanks. It seems to be a good solution. But at the same time it is not cross-version - as you say it is for Excel 2010. I found another solution - to add a space in the front of the number like string. Hope it will work for different versions. – Jacobian Sep 15 '13 at 20:53
  • 2
    @Jacobian: Does `="123123123123"` work? – Ry- Sep 15 '13 at 22:38
  • Yes, it also works. Though an extra space is the simplest way to do things. – Jacobian Sep 16 '13 at 03:40