Link to home
Start Free TrialLog in
Avatar of Michael Miller
Michael Miller

asked on

Paste Special Causing Workbook Corruption

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 a Microsoft solution, 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.


Thank you for your time,

Michael
Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image

I import data into Excel regularly and the numbers are often stored as text. This is noticeable because the cells have a small green triangle on them.The simplest way to convert the "text numbers" is to select the cells with the green triangle and click on the small green arrow which opens an option to convert numbers stored as text to real numbers.

You can see the triangles in the image on the link provided.
Avatar of Michael Miller
Michael Miller

ASKER

Roy,

Thanks for the comment.  I do see the triangles on the imported data in the spreadsheet.  However, there can be hundreds of rows and multiple columns in a given spreadsheet.  Changing each affected cell one by one is not viable option.  I need to be able to bulk change the text-formatted numbers.  

Thanks again.
You don't need to change one by one . Just select the ones in an affected column, then use the option.

Select the first cell in the column

Ctrl+Shift+ Down Arrow will select all contiguous cells in the range
ASKER CERTIFIED SOLUTION
Avatar of Michael Miller
Michael Miller

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I've not come across this error before, I think it might be the sheer volume of cells involved.

Maybe using VBA might help, do you have an example workbook that you can upload?
I've attached one of the sheets we've been testing with.  This one has only 712 rows, but the same result with the paste special.  

Thanks,
Copy-of-inventory-original-data_2.xlsx
i can't see any number errors in the example. I would say that you only need to convert columns C, D and H. These are currently formatted as General.
HI Michael

Have we taken this as far as we can. I can provide code to unmerge and maybe convertin with VBA might prevent the issue.
OP says I have provided a viable workaround.
That's not the solution