0
I have a file with 50000 rows and 500 columns approx. with data populated. Though half of the cells in the sheet seem blank but the file looks heavier (50mb) which should not be. When I count the blank cells, Excel shows it counts. Don't have any idea what do we call such cells because there is something maybe special character or formatting. In one forum I saw a solution where the user mentioned to find blanks and replace it with "~" (tilde sign) and then replace the tilde sign again with blanks by having put "~~" in Find and blanks in replace. This does the job but however in a file containing so much data it takes hours to replace blanks with tilde and then replace again with blanks for additional some hours.
Looking for a better solution if anyone knows to replace such cells and what do we describe such cells. Thanks in advance.
Irvin
Posted 2020-02-28T11:16:07.703
Reputation: 51
These files are often .csv exports gone wrong. A .csv file is a textfile. Open them with notepad++ or similar, and remove all empty lines at the bottom, save, and presto. An even better solution is to ask those that make the CSV export to fix it at the export itself. – LPChip – 2020-02-28T11:34:35.897
@LPChip Ok sure, thanks for the response. Don't know if it's indeed an export but I got a 50mb xls file. So if it were a csv then would have surely tried the solution. – Irvin – 2020-02-28T11:59:29.423
Ask from the person you got the XLS from if they have it in the original CSV. Chances are they have. Then explain there are many blank cells that makes the file unnecessarily large. Chances are they can fix it and the problem won't return for you anymore. – LPChip – 2020-02-28T12:30:24.063