I have adopted a nightmare workbook today that takes several minutes to open and is 9mb in size. There appears to be some type of corruption in a specific worksheet that I have been unable to identify / fix.
I have tried all manner of things to repair this without success. The w/s is primarily data, with few formula’s, no links, just data storage basically. There are approx 18 columns and 2000 rows of data.
My latest attempt at repair (which partially worked) was as follows:
Selected all cells, copy and paste as special values to remove all formula’s.
Selected all cells, removed all wrapped text and merged cells.
Selected all 18 columns (except 2 date columns and 2 numerical value columns) and formatted the remainder as text. Within the remaining text columns, there are 16 digit account numbers that were previously formatted as general.
Copied the dataset to a new w/s in a new book.
Saved the new book as a new xls file.
Saved the new xls file as a text (tab delimited) file, and closed the text file.
Opened the text file and saved as a new xls.
Used the formatting paintbrush to copy the formats back over the “text like†data in the newest xls file.
Saved, reopened (really fast) hey presto, worked, corruption gone.
My problem is that the 16 digit account numbers (which were previously changed to a text format) are corrupted in the new book, by corrupted, what I mean is:
4048653344556677 becomes (in the formula bar) 4048653344556670 (the last digit is dropped in favour of a zero), and visibly in the cell, shows 4.04971E+15.
Any idea’s how I can repair this?
Thanks
Repair corrupt worksheet
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
Repair corrupt worksheet
Nathan
There's no place like home.....
There's no place like home.....
-
- Administrator
- Posts: 78493
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Repair corrupt worksheet
The number 4048653344556677 contains 16 digits. Excel is only precise to 15 digits, so when you try to store a 16 digit number as a number, it will by necessity be rounded. If you need to keep 16 (or more) digits, you should use text format, because that will store the number "as is".
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
Re: Repair corrupt worksheet
Thanks, I was not aware that anything over 15 would be rounded, I'll keep this in mind for future.HansV wrote:The number 4048653344556677 contains 16 digits. Excel is only precise to 15 digits, so when you try to store a 16 digit number as a number, it will by necessity be rounded.
Ok, the 16 digit numbers have been incorectly stored as general instead of text historically, but I reformatted them as text prior to saving the file as a text file, and then back to excel. The data in the text file looks right, resaving it to excel appears to cause the problem. Going forward, I will use the text format, but I need to find a way to rid myself of this corruption whilst maintaining the data?HansV wrote:If you need to keep 16 (or more) digits, you should use text format, because that will store the number "as is".
Nathan
There's no place like home.....
There's no place like home.....
-
- Administrator
- Posts: 78493
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Repair corrupt worksheet
Formatting as text should prevent data loss.
Added Oh wait - you mean when importing the text file? The Import Text Wizard lets you specify the format for each column.
Added Oh wait - you mean when importing the text file? The Import Text Wizard lets you specify the format for each column.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
-
- Administrator
- Posts: 78493
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
Re: Repair corrupt worksheet
Aah...
No, I saved the excel file as a text file, then saved the text file as an excel file. Are you saying that I need to save the excel file as a text file and then open the text file in excel using the import wizard. I had not considered that, but I think that should work. Thanks
Some days, I really hate excel, and today is one of them.
No, I saved the excel file as a text file, then saved the text file as an excel file. Are you saying that I need to save the excel file as a text file and then open the text file in excel using the import wizard. I had not considered that, but I think that should work. Thanks
Some days, I really hate excel, and today is one of them.
Nathan
There's no place like home.....
There's no place like home.....