Nov 28 2022 02:56 AM
Good day
I have a problem with excel changing cell format from General to Custom from a specific line number when exporting a report into excel. The data that is being exported is fine from the first line to line 29, but from line 30 it changes the cell format to custom and therefor the result is I get dates instead of the numbers exported from that line down. As per the attached image, the column on the right is where it changes. What could be the cause of this?
Any help would be greatly appreciated.
Kind Regards
Ryno
Nov 28 2022 03:35 AM
Excel always tries to interpret a value as a date, if possible, even if that was not your intention.
Excel cannot handle dates before the year 1900, so for example 1893-2 is treated as a text value, and hence left-aligned by default.
But it interprets 1900-2 as a date (the first of February, 1900), so it changes the format to a date format and right-aligns the value.
How to prevent that depends on what kind of file you're importing in Excel.
Nov 28 2022 03:44 AM
@Hans Vogelaar thank you for your response. It is a report on a ERP system that is exported as csv. On other computers it exports perfectly fine but this specific one it does not. Is there a setting I can change so that Excel can read all numbers as text as a default perhaps?
Kind Regards
Ryno
Nov 28 2022 04:29 AM
Change the extension to .txt.
If you open the file in Excel, it'll start the Import Text Wizard.
In the last step of this wizard, you can specify that the column with values such as 1900-2 must be imported as Text.
Nov 28 2022 07:56 AM
And there is no need to change the file extension to ".txt" first.
Simply click Data > From Text to open the Import Text File dialog box.
Nov 28 2022 11:34 PM