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
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
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.
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
Select the first cell in the column
Ctrl+Shift+ Down Arrow will select all contiguous cells in the range
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
Maybe using VBA might help, do you have an example workbook that you can upload?
ASKER
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
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.
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
You can see the triangles in the image on the link provided.