I have an Excel file that’s… ‘interesting’. First, it crashes the program when I try to do text-to-columns. Then I remember it has non-display characters. Since they’re not all ALT+010, I have to copy it into Word and do a Replace manual line break to pipes. Then I have to copy it back into Excel. It takes a long time to copy and paste a file (about 4,000 records in this case).
But here’s the actual problem: I have to copy-and-paste cells, so… I copy the cell or cells! With the intent of pasting them! But for this one file, even copying one cell hangs up Excel for about two minutes before I can paste it/them. Why? Is there some other non-display character that makes it do this? If so, is it findable in Word? If it’s not that, then what? I copied the entire file (3,999 records, 19 columns) and pasted it as text in a new spreadsheet. It’s still hanging up.
Not sure if it will help but you might consider pasting into Notepad++.
It is free and you can tell it to show hidden characters ( Menu: View → Show Symbol → Show All Characters). It is a very useful program. Every PC should have it.
Can you export the spreadsheet to a comma delimited file and then upload that comma delimited file back into a new spreadsheet? I am not sure but that may strip all the non-display crap out and just move the data. Formatting and all that will be lost.
Again, just guessing. I do not know if that will solve the problem (since I am still not sure what is really causing this problem but this could help troubleshoot).
You also may consider opening Task Manager (CTRL+ALT+DEL) and see if CPU or memory usage go nuts when copying. It won’t solve the problem but may provide some clues.
Can’t download or can’t install? If you can download stuff, you can use many apps without installation (typically called “portable” because one of the uses is to dump them on a USB stick). Notepad++ comes in a portable version:
That would be my recommendation as well. The o.p. should be able to install it with just user permissions (will only be accessible to him but as long as he is the only user it doesn’t matter).
I’d ask where the o.p. gets these crazy files but frankly I’ve seen worse. A couple of years ago I had someone give me an Excel document that was supposed to be just time history data (just sampled instrumentation) that they decided to glom together into a spreadsheet instead of delivering individual text or binary files. It turned out that someone wrote some kind of bizarre frequency filtering macro that iteratively ran across the spreadsheet so that every time you opened it it spent ten minutes “filtering” data, because that is what everyone is missing in their life, I guess. I finally gave up trying to open the fucking thing and imported the data directly into Python using openpyxl, and since I figured out how to do it haven’t bothered opening up another data dump directly in Excel again. Excel is a great tool for certain types of task, but a data processing and mangling tool it is not.