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
Michael MillerIT EngineerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
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.
Michael MillerIT EngineerAuthor Commented:
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.
Roy CoxGroup Finance ManagerCommented:
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
Maximize Customer Retention with Superior Service

The IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more to help build customer satisfaction and retention.

Michael MillerIT EngineerAuthor Commented:
If I start in an affected cell your solution does work; this was a step I was missing before.  However, even when I convert all of the number-as-text cells the paste special function still causes the spreadsheet to corrupt.  

This is my current conundrum:
The user will have to use the green arrow to convert two columns to numbers from text.  Remove or un-merge the bottom row of the spreadsheet.  Sort a third column (the spreadsheet today has 38,000 rows and the third column is not contiguous) and correct the text numbers in that column. Replace the bottom row.  Then highlight the entire sheet format as number and remove the decimal places (because even at this point a paste special causes the workbook to corrupt).

In  Office 2016 Pro Plus and Office 2013 the user has to enter 1 into a cell, copy that cell, highlight the active cells, paste special multiply to get the same results.  

Roy, your solution is a viable work around, but I need to correct the paste special error.  In your experience do you know why Excel would corrupt using a paste special function?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Roy CoxGroup Finance ManagerCommented:
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?
Michael MillerIT EngineerAuthor Commented:
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
Roy CoxGroup Finance ManagerCommented:
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.
Roy CoxGroup Finance ManagerCommented:
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.
Roy CoxGroup Finance ManagerCommented:
OP says I have provided a viable workaround.
Roy CoxGroup Finance ManagerCommented:
That's not the solution
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.