Paste Special Causing Workbook Corruption

Question

My company is in the process of changing to Office 365, and one of my users has run into an Excel issue that I can't seem to overcome.

Situation:

User exports a report to Excel from our accounting software.  The spreadsheet contains several columns that has numbers set as text.  The user's solution to this is to multiply the spreadsheet by 1 and convert the cells and text to numbers. This is aMicrosoftsolution, although, I had never seen it used before.  

The problem is that once the file is saved and reopened Excel reports that the workbook is corrupt and needs to be repaired.  The repaired book has removed all of the letters from the spreadsheet.

Diagnostics:

Is replicated with several different reports and multiple computers

Does not occur with Office 2013 Pro Plus or 2016 Pro Plus (even on systems that crash with 365)

No error messages in Event Viewer

All Windows, driver, and Office updates have been applied

Office has been reinstalled

Excel cache and all temp folders have been cleaned out

The first option in the link to use the text to columns function, but one of the columns uses a merged cell so it won't work for the entire spreadsheet.

Answer 1

Sorry Michael,

I tested here, when I copy the text-number to a new sheet, and I click them, the  small green error triangles at the top corner shows up, when I hover, it let me choose to fix the cell and convert to numbers.

If it’s possible please test it on your machine see if it works.

Regards

Alex

Thanks for checking that and I am able to get that to work.  However, with some of the larger workbooks this solution adds several minutes to a job that would otherwise take 30 seconds.  Would you care to try the paste special operation and see if that causes your test book to corrupt as well?  Assuming that you are using Office 365.

Thanks,

Michael

Answer 2

Sorry Michael,

I tested here, when I copy the text-number to a new sheet, and I click them, the  small green error triangles at the top corner shows up, when I hover, it let me choose to fix the cell and convert to numbers.

If it’s possible please test it on your machine see if it works.

Regards

Alex

Answer 3

Alex,

Those are the same steps I am taking.  The program he is using exports the numbers as text, so the cells show the small green error triangles at the top corner.  You can see the difference by setting your test cells to x.xx and then multiply those numbers by one. 

Thanks,

Michael

Answer 4

Hi Michael,

Thank you for all the completed information you provided.

I tried to convert my text numbers by multiply by 1 using Paste Special in office 365 Business but it seems did not convert my text to numbers.

So could please share the steps how to multiply the spreadsheet by 1 and convert text to numbers?

Regards,

Alex

Answer 5

I suggest reporting this to Excel using the frown face under Help.