Excel rounding value 7,256E+18

Question

That old Excel cell formatting question is causing me problems now. I use the Laravel framework and to export the data I use the Laravel Excel.

I’m formatting the cell to type text and yet Excel keeps rounding the values and changing the end to 0000. Follow my example.

Passing the cell to text type.

$sheet->setColumnFormat(array(
'B' => '@'));

Result in spreadsheet.

7,256E+18 => 7256001273850860000 // valor que sai na célula no excel
             7256001273850860168 // valor que era para ser gerado

If anyone can help, I really appreciate it. Thanks..

Answer 1

This is because Excel does the rounding after 15 digits as it uses the Floating-point arithmetic. Then the data should be shown as Text (String)

Option 1

Format Cells to Text Manually by selecting cells, then enter the number with the cell already formatted, as in the image:

Option 2

Excel VBA code to transform the formatting of the entire spreadsheet into text

With Sheets(1).Cells 'Para primeira planilha
.NumberFormat = "@"
End With

Or with the name of the spreadsheet

With Sheets("Planilha1").Cells
.NumberFormat = "@"
End With

Option 3

Reading about Lavaravel, it can export to CSV files.

Then you can export the files to CSV and use an Excel-VBA code to import them as String to the spreadsheet.

I suggest contacting Laravel and asking for some change in the code that creates the Excel spreadsheets, to make the formatting of cells in Text. Or open a Content on Github

I suggest reading this issue of Laravel’s Github: Laravel 4.2/Laravel-Excel 1.3: Numbers Formatted as text still Appearing as number #613

Answer 2

Well, to "solve" the problem, when I go to play the value in the spreadsheet, I concatenate a blank space, so excel does not break the format.

$sheet->row($cont, array($val.' ')); 

Answer 3

Put the value in this way ="7256001273850860168", as if it were a function, then yes it will identify as a string.

Or format the field as text first to then insert the value, because as soon as you enter the value in excel it puts the zero at the end, but if you format as text and then assigns the value works.