0

I am having a little bit of a problem with Excel. I am typing into a cell a barcode number for my products but every time I leave the cell it automatically formats the cell as below.

Number I want to enter: 5028252188661
Result after entering: 5.02825E+12

Saving the document as a normal .xls or .xlsx file I can overcome this problem by changing the format to a number, removing the decimal places.

My main issue is when I convert the file to a CSV, the column is automatically formatted back and keeps doing this even after I change and save the file.

If anyone is able to help me with this, it would be greatly appreciated.

Dave
  • 25,297
  • 10
  • 57
  • 69
Floyd
  • 27
  • 1
  • 3
  • The file is originally saved as a .xls format. I then save it as a CSV for my work or to export to MySQL through PHPMyAdmin. – Floyd Dec 13 '13 at 13:51
  • And during which part of the process is the format changing? Your comments says XLS -> CSV -> MySQL. I'm not sure when you're reloading it in Excel. Or are you saying that the formatted number (5.02825E+12) is what is being generated when saving to CSV? Can you please just open the CSV with notepad to ensure it's not just the way Excel is displaying it? – Dave Dec 13 '13 at 13:55
  • have you tried changing the format cell to text before entering barcode data – Antony Dec 13 '13 at 13:56
  • Part 1, create the xls with number. Part 2, convert the sheet to CSV. Part 3, Upload to MySQL. – Floyd Dec 13 '13 at 13:59
  • Andrew, after converting to a CSV file, just test it in notepad and see if what value is exported. – Dave Dec 13 '13 at 13:59
  • 5028252188661 is shown in Notepad++ – Floyd Dec 13 '13 at 14:03
  • I'm going to assume at the moment MySQL however I am just testing a new method changing the data type to text for barcode. – Floyd Dec 13 '13 at 14:11
  • @Andrew, based upon your comments in other answers this is firstly, a terribly worded question! It actually has NOTHING to do with Excel and I've voted to close as it's unclear. Secondly, the issue seems to be about the database (which you only mention in the comments)! Therefore, it's a db or programming question, and off topic here (although I can't vote to close twice). – Dave Dec 13 '13 at 14:13
  • Dave Rook - Regardless of the motives once I have the file as a CSV, this question is still relevant. I have taken fully into account the advice given and have only mentioned my intentions for after the file is converted to CSV and answered what people have asked. – Floyd Dec 13 '13 at 14:17
  • Then, the answer is, the value you're seeing is only how Excel formats it!The value itself is actually the full number, not the notation (as per my post). – Dave Dec 13 '13 at 14:21
  • Upvoting since this question is more descriptive and more readily found than "how to turn off scientific notation" as far as I'm concerned. I would have called it "exponent format" – omatai Jun 05 '18 at 22:53

2 Answers2

1

The answer is no! This is the behaviour by design I'm afraid to say! Source

There may be some work arounds using templates. See the comments as I've marked it as a duplicate.

I will also point out, that it may not actually do what you think it's doing (or what it's showing). When Excel loads the CSV, it formats it to the scientific notation, however, that's just the formatting, the actual value is untouched. To test it, load the CSV in notepad and see.

Dave
  • 25,297
  • 10
  • 57
  • 69
1

Select the entire column in your excel sheet that contains the barcode number. Right-click the column-header and choos 'Format cells'. In the dialog that apperars, choose Text. Excel should now never reformat the contents.

You do need to perform this action everytime you open your CSV in Excel.

R-D
  • 2,546
  • 1
  • 19
  • 22
  • This is correct, however when exporting to a database, the column is reverted back and is displayed on the database as 5.02825E+12. – Floyd Dec 13 '13 at 13:57
  • What kind of database, what are the properties for the barcode column in the database? It sounds more like a problem in the database or the client application than with Excel. – R-D Dec 13 '13 at 14:03
  • varchar 255, will change to text. – Floyd Dec 13 '13 at 14:04
  • Varchar 255 should suffice, maybe PHP is treating it as a number instead of text? – R-D Dec 13 '13 at 14:05
  • Roland Van Dorn - There is a solution to this that I have just found, save the sheet as a OpenDocument Spreadsheet (.ods). Then export through PHPMyAdmin. This will create a new sheet and you can name your MySQL columns through your spreadsheet. – Floyd Dec 13 '13 at 14:32