Search the Site

Sponsors

bottom corner

Prevent Excel from recognizing a string as a number when opening CSV files

When using Microsoft Excel to open a CSV file, at times a string may appear like a number, causing Excel to open it in a wrong format. This note offers one solution for this problem.

This page is filed under keyword(s): excel.

I often use CSV format to transport data, largely because its text nature makes the data extremely portable across different platforms and applications. Yesterday when a colleague opened a CSV file from me using Excel, one of the fields I provided was displayed incorrectly. Two examples of the troubles Excel are listed below.

Value Provided in CSVShown by Excel
0E360
005555

In the first case, Excel thought the value was a scientific notion; in the second case, Excel treated it as a number. In both cases, they were both wrong.

This issue also happens frequently when displaying ZIP codes for addresses in the United States, eg. Excel showing the intended "04001" as "4001".

Because this is an Excel-specific behavior, I consider the following fix to be a work-around rather than a permanent solution; nevertheless, it works well for me. Instead of a simple CSV format as I had originally:

"aaa","bbb","ccc","0055","ddd"

I inserted an equal sign in front of it. This forces Excel to think that I am writing a formula that outputs a string, thus solving the Excel issue.

"aaa","bbb","ccc",="0055","ddd"

Did you find this page useful? Please consider browsing other articles or subscribing to the RSS feed to keep up with latest.

This page is filed under keyword(s): excel.
Author: C. Peter Chen
Last updated: 14 Jan 2011

bottom corner