Repair corrupt worksheet

[VegasNath]

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

[HansV]

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".

[VegasNath]

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.

Thanks, I was not aware that anything over 15 would be rounded, I'll keep this in mind for future.

If you need to keep 16 (or more) digits, you should use text format, because that will store the number "as is".

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]

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.

[VegasNath]

But.... I did that.

[HansV]

I edited my reply while you posted yours.

[VegasNath]

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.