Wednesday, April 17, 2019

Excel converts value from Text to Date format on a CSV file.

This happens on a CSV Excel file where a value initially formatted as Text is converted by Excel to the Date format when the file is saved and evenutually reopenned.

 

For example:

1. Open MS Excel.

2. In cell A1 using your mouse right click > Format Cells > Text > Click OK.

3. Input Sept 2013

4. Save the file as CSV (Comma delimited) type and reopen it.

 

Result: Cell A1 now shows Sep-13 or 9/1/2013 in a custom format mmm-yy. If the CSV file is re-saved it will retain the incorrect format and an invalid reference error will be received if the same is used on a CSV import.

 

To stop MS Excel from automatically converting the value from Text to a Date format put an equal sign with a double quote on the value. It will force the data to use the Text format.

 

In the above example Sept 2013 should be keyed as =" Sept 2013". Note that there is a space in between the double quote " and the value Sept.

No comments:

Post a Comment