Format cells - dates to numbers

Highlighted
New Contributor

Hi all,

when re-formatting csv data to a table in excel, values above 1 are always formatted as dates (Feb 2nd instead of 2.2). Changing format in format cells menu then changes the values to "43831". Any idea how I can get the right format for a row of 50,000 lines?

Thanx a lot in advance!

 

2 Replies
Highlighted

@Alex_T110 

The workaround is not to open csv in Excel, but import csv to Excel, parser will work by different way. Depends on Excel version and your preferences you may use legacy import, in it on third step assign Text to such columns. The same if import by Power Query, transform and use Text type.

Highlighted

@Sergei Baklan 

Thank you very much for the quick answer, Sergej!

The problem comes up without importing the csv data to xls, but when separating comma-separated data in csv via the "data" menu in separation by columns.

It's all part of a training, so I also asked a mentor for help. But he definitely is not as fast as you are